#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0

    Summing 2 fields from 1 table. To Join or to Union..??


    Team,

    I am trying to write a query to display the following:

    Pickerid, Pickername, Premiumcount, Rubbishcount

    Each Pickerid has multiple entries in the table. ie

    Pickerid Pickername Grade
    1 test1 PR
    1 test1 AP
    1 test1 CA
    1 test1 AM
    1 test1 AA

    and so on....

    I am trying to get a grouped count which uses this logic.

    Each Pickerid I need to count the number of "Grade" records equal to PR and AP. This we shall call Premiumcount, I also want to group the other "Grades" which are equal to "CA", "AM" & "AA" which we shall call Rubbishcount

    These records all reside on the same table.

    This is the result for the above I'm after.

    Pickerid PickerName PremiumCount RubbishCount
    1 test1 2 3

    Each of my queries work individually but when I join them / union them I get no result.

    I appreciate your help.

    SELECT Q1.Pickerid, Q1.Pickername, PremiumCount, RubbishCount
    FROM (SELECT Pickerid, Pickername, Count(*) AS PremiumCount FROM mergeddatabase WHERE (((Currentdate) between [Enter First Date Required:] and [Enter Last Date Required:]) and (grade = "PR" or grade = "AP" )) GROUP BY grade, PICKERID, PICKERNAME) AS Q1, (SELECT Pickerid, Pickername, Count(*) AS RubbishCount FROM mergeddatabase WHERE (((Currentdate) between [Enter First Date Required:] and [Enter Last Date Required:]) and (grade = "CA" or grade = "AM" or grade = "AA" )) GROUP BY grade, PICKERID, PICKERNAME) AS Q2
    WHERE Q1.Pickerid = Q2.Pickerid;

    Sorry for the formatting.. looks fine on my editor.

    Regards
    Mark
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Code:
    select Pickerid, 
           Pickername, 
           sum(case when grade in ('PR','AP') then 1 else 0 end) as PremiumCount,
           sum(case when grade in ('CA','AM','AA') then 1 else 0 end) as RubbishCount
      from mergeddatabase 
     where Currentdate between [Enter First Date Required:] and [Enter Last Date Required:]
       and grade in ('PR','AP','CA','AM','AA')
     group
        by PICKERID,
           PICKERNAME
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0

    Huge thanks


    Swampboogie. Thanks heaps for your prompt response. I shall try this on Monday. I'll drop you a note. I've not heard of CASE before.
    Thanks again

IMN logo majestic logo threadwatch logo seochat tools logo