Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: problem with aggregate queries

    I have this query.. It works but doesn't give me my requirements...

    I need to count the GR where it is equal to p4 and group it by asst and it gives me right.. My problem is integrating the count of gr='cs' in the same query is it possible so that i could get also the count of where gr='cs'

    SELECT COUNT(gr) AS p4asst, a.asst
    FROM soc s inner join asst a on s.pr=a.pr
    WHERE (GR = 'P4')
    AND datepart(year,enter) < 2009
    group by a.asst

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT p4asst = SUM(CASE WHEN gr = 'P4' THEN 1 END), csasst = SUM(CASE WHEN gr = 'cs' THEN 1 END),  a.asst
    FROM 	soc s inner join asst a on s.pr=a.pr
    WHERE GR IN('P4', 'cs')
    AND enter < '20090101'
    group by a.asst
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2006
    Posts
    87
    Great thanks it did the trick...


    Quote Originally Posted by pootle flump
    Code:
    SELECT p4asst = SUM(CASE WHEN gr = 'P4' THEN 1 END), csasst = SUM(CASE WHEN gr = 'cs' THEN 1 END),  a.asst
    FROM 	soc s inner join asst a on s.pr=a.pr
    WHERE GR IN('P4', 'cs')
    AND enter < '20090101'
    group by a.asst

Posting Permissions

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