Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

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

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
utlonghornjulie
Asked:
utlonghornjulie
  • 4
  • 4
1 Solution
 
brad2575Commented:
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
 
utlonghornjulieAuthor Commented:
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
 
brad2575Commented:
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
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.

 
utlonghornjulieAuthor Commented:
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
 
brad2575Commented:
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
 
utlonghornjulieAuthor Commented:
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
 
brad2575Commented:
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
 
utlonghornjulieAuthor Commented:
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.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now