Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: A Bit Confused on This Query

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 --
    Code:
    SELECT COUNT(*) AS rows
      FROM daTable
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    46
    Quote Originally Posted by r937
    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 --
    Code:
    SELECT COUNT(*) AS rows
      FROM daTable
    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!
    Last edited by rockdave35; 03-10-09 at 15:56.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rockdave35
    Is there anything that my 2nd query is doing differently than the first query?
    yeah, it's counting distinct values rather than rows

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2009
    Posts
    46
    Quote Originally Posted by r937
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your assessment of the query is correct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •