Hi Eric, You can part of the way there with GROUP BY:
SELECT node_name,class_name,COUNT(*) FROM archives GROUP BY node_name,class_name This will give you one row per (node_name,class_name) tuple. In order to get row values as columns, though, you need to do a pivot (aka crosstab), which I'm not sure is possible in through the DB2 interface in dsmadmc. If you run the query w/ "-dataonly=yes -tab" you can import into a spreadsheet easily and do the pivot there, though. Hope that helps! On Fri, Mar 12, 2021 at 03:27:29PM +0000, Loon, Eric van (ITOP NS) - KLM wrote: > Hi everybody, > > I'm trying to figure out how to create a SQL query to retrieve the amount of > files, per management class, per node in just one query. The ideal output > would be: > > Nodename Class1 Class2 Class3 > Mynode1 1234 475859 3645895 > Mynode2 12345 274368746 8948382348 > > If you select a count(*) from archives where class_name='CLASS1' you will > only get the amount stored for Class1, so I will have to be able to combine > multiple select count(*) from archives where statements in one single query. > Thank you very much for your help! > > Kind regards, > Eric van Loon > Air France/KLM Storage & Backup > ******************************************************** > For information, services and offers, please visit our web site: > http://www.klm.com. This e-mail and any attachment may contain confidential > and privileged material intended for the addressee only. If you are not the > addressee, you are notified that no part of the e-mail or any attachment may > be disclosed, copied or distributed, and that any other action related to > this e-mail or attachment is strictly prohibited, and may be unlawful. If you > have received this e-mail by error, please notify the sender immediately by > return e-mail, and delete this message. > > Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its > employees shall not be liable for the incorrect or incomplete transmission of > this e-mail or any attachments, nor responsible for any delay in receipt. > Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch > Airlines) is registered in Amstelveen, The Netherlands, with registered > number 33014286 > ******************************************************** -- -- Skylar Thompson (skyl...@u.washington.edu) -- Genome Sciences Department (UW Medicine), System Administrator -- Foege Building S046, (206)-685-7354 -- Pronouns: He/Him/His
