Hey guys,

I'm hoping someone can explain this query to me. Here is the query:

SELECT CASE WHEN EMPR_PLAN_CD = 'N' THEN 'NON-CONTRIBUTORY TOTAL'
WHEN EMPR_PLAN_CD = 'K' THEN 'CONTRIBUTORY'
END AS PLAN_CODE,
COUNT(*) AS TOTAL
FROM DSNP.PR01_t_MBR_HIST
WHERE MBR_HIST_SVC_CR_DT = '2009-01-31'

GROUP BY EMPR_PLAN_CD


My main question is how does the COUNT know to count the K's and the N's. I thought count(*) would count the entire number of records in a table.

Thanks.

no, COUNT(*) counts the number of rows in each group

in the above query, the Ns are one group, while the Ks are another group

(there is a third group, for plan codes that are neither K nor N, but let's not confuse you any further at the moment)

see the GROUP BY clause? that determines the groups

when you see a query like this --there is no GROUP BY clause, and in that case, all the rows form a single group -- so again, COUNT(*) counts the number of rows in each group, but since there's only one group, it effectively gives you the count for the whole table

make sense?

I think I understand now. If I do a group by, I'm going to get a separate count for each unique value in the group, correct?


Also, compare this query with the one above:

select count (distinct mbr_ssn_nbr) as total
FROM DSNP.PR01_t_MBR_HIST
where empr_plan_cd = 'K'
and MBR_HIST_SVC_CR_DT = '2009-01-31'



In the table, every record has a SSN number and a empr_plan_cd. I get similiar but different count totals between the two queries. I should get the same count total for K.

Is there anything that my 2nd query is doing differently than the first query?

Thanks for the help!

yeah, it's counting distinct values rather than rows

So if my table had this:


SSN EMPR_PLAN_CD


11122 K
11122 K
11122 K


I would expect the first query to count 3 for K, and the 2nd query to count 1 for SSN. So I should be receiving a much higher K count on the first query since the same SSN can have numerous records. Looks like I'll have to some research.

your assessment of the query is correct

by the way i stayed out of your tek-tips thread because you seem to be getting good advice over there too

however, one thing puzzles me... here, you have a DB2 problem, but over there, it's a SQL Server problem

and yet they're the same tables

what gives?