Solved

Trying to get a count of rows in a table grouped by name

Posted on 2008-10-23
8
171 Views
Last Modified: 2012-05-05
I am trying to write a select statement that returns three columns: Username, a count of rows in the table where status = 3, and a count of rows in the table where status = 999. I want the counts to be grouped by the Username column. For example, if User1 has 3 rows of status = 3 and 9 rows of status = 999, and User2 has 1 row of status = 3 and 5 rows of status = 999, then the query should return a result set of:
User1     3     9
User2     1     5
Total       4     14

Please help!
0
Comment
Question by:utlonghornjulie
  • 4
  • 4
8 Comments
 
LVL 16

Expert Comment

by:brad2575
Comment Utility
select username, Case When status = 3 Then count(userName) END as Count3,
Case When status = 999 Then count(userName) END as Count9
From TableName
Group By UserName
0
 
LVL 3

Author Comment

by:utlonghornjulie
Comment Utility
Ok that sort of worked. But I don't want the nulls to show up in the result set.
Here is the result set that I get from the query below.

Analyst_brokerrep NeedApproval NotApproved
Jess               NULL                   NULL
Brian               NULL                   NULL
Casey               NULL                   NULL
Jess               NULL                   NULL
Jonathan               NULL                   NULL
Stephen               NULL                   NULL
Casey               6                   NULL
Jess               2                  NULL
Stephen               3                  NULL
Casey               NULL                   6
Jess               NULL                   1
Stephen               NULL                   2

I want the result set to look like this:

Analyst_brokerrep NeedApproval NotApproved
Casey                6                   6
Jess                2                   1
Stephen                3                   2



select analyst_brokerrep, Case When [status] = 3 Then count(status) END as NeedApproval, 

Case When [status] = 999 Then count(status) END as NotApproved

FROM         tbl_Offer_1_Price INNER JOIN

                      EnrollCustomer ON tbl_Offer_1_Price.DealCapID = EnrollCustomer.DealCapID

where analyst_brokerrep is not null

Group By analyst_brokerrep, [status]

Open in new window

0
 
LVL 16

Expert Comment

by:brad2575
Comment Utility
do you want BOTH to have a number in it?  So the 9 AND the 3 have to have a count?  Or does just one need a count?

If just one needs a count this should work:

select username, Case When status = 3 Then count(userName) END as Count3,
Case When status = 999 Then count(userName) END as Count9
From TableName
Where status IN (3, 999)
Group By UserName
0
 
LVL 3

Author Comment

by:utlonghornjulie
Comment Utility
If a user has both 3 and 999, then I want both counts to show up on one row for that user. If a user only has one or the other, then only show the one where he has a count.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 16

Expert Comment

by:brad2575
Comment Utility
Sorry forgot had to group by status as well.  This should work.

Select analyst_brokerrep, NeedApproval, NotApproved
From (
      select analyst_brokerrep, Case When [status] = 3 Then count(status) END as NeedApproval,
      Case When [status] = 999 Then count(status) END as NotApproved
      FROM         tbl_Offer_1_Price INNER JOIN
                                      EnrollCustomer ON tbl_Offer_1_Price.DealCapID = EnrollCustomer.DealCapID
      where (status IN (3, 999)) AND (analyst_brokerrep is not null)
      Group By analyst_brokerrep, [status]
) as Z
Group By analyst_brokerrep
0
 
LVL 3

Author Comment

by:utlonghornjulie
Comment Utility
That does not work. When I try and run the above query, it gives me the following error:

Column 'Z.NeedApproval' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
LVL 16

Accepted Solution

by:
brad2575 earned 500 total points
Comment Utility
try this

Select analyst_brokerrep, sum(NeedApproval), sum(NotApproved)
From (
      select analyst_brokerrep, Case When [status] = 3 Then count(status) END as NeedApproval,
      Case When [status] = 999 Then count(status) END as NotApproved
      FROM         tbl_Offer_1_Price INNER JOIN
                                      EnrollCustomer ON tbl_Offer_1_Price.DealCapID = EnrollCustomer.DealCapID
      where (status IN (3, 999)) AND (analyst_brokerrep is not null)
      Group By analyst_brokerrep, [status]
) as Z
Group By analyst_brokerrep
0
 
LVL 3

Author Comment

by:utlonghornjulie
Comment Utility
Thanks it worked!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now