Solved

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

Posted on 2008-10-23
8
178 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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