How do I get counts of multiple records from a single table using db2 query?
Suppose I want to get the count of 1 record am using:
select count(*) from schema.table where record value='x'
What I need is a count of multiple records from the same table in separate rows for each record. I am trying something like:
select count(*) from schema.table where record in('x','y','z')
The queried result combines the value into one single value in a single row, which I don't want.
I almost agree with the Mureinik. You can add a WHERE clause to get multiple row counts from only those records you want, e.g. (x, y, z)
SELECT record, COUNT(*) AS 'count'
FROM schema.table WHERE record IN ('x', 'y', 'z')
GROUP BY record
result:
------------------
| record | count |
------------------
| x | 100 |
| y | 150 |
| z | 50 |
------------------
WHERE record in ('x','y','z') There no point in the group by if you're only selecting a single record value.
The group by syntax breaks the table up into groups, and allows you to perform aggregate functions (count, in your case) on each one separately:
SELECT record, COUNT(*)
FROM schema.table
GROUP BY record
