?
Solved

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

Posted on 2008-10-23
8
Medium Priority
?
194 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 22787516
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
ID: 22787659
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
ID: 22787792
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 3

Author Comment

by:utlonghornjulie
ID: 22787848
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
 
LVL 16

Expert Comment

by:brad2575
ID: 22787894
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
ID: 22787921
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 2000 total points
ID: 22787978
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
ID: 22787992
Thanks it worked!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

770 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