Solved

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

Posted on 2008-10-23
8
176 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

803 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