CODEWITHSUNDEEP
sql-servergroup-by
milo2010
milo2010

Reputation: 91

GROUP BY Problem

I have a SQL Server 2005 table called Users:

UserID | Date        | Name | Issues | On    | Off   | Value
1      | 02/02/2010  | John | 3      | True  | False | 75
2      | 07/23/2010  | Nate | 7      | False | True  | 50
3      | 02/12/2010  | John | 5      | False | True  | 45
4      | 01/29/2010  | John | 8      | True  | False | 65
5      | 09/01/2010  | Nate | 6      | True  | False | 30
6      | 12/07/2010  | John | 2      | False | True  | 40
7      | 07/18/2010  | Nate | 10     | True  | False | 80

I want to get the SUM of Issues for each Name, the SUM of Value when On is true, the SUM of Value when Off is true between given dates:

Name | No of issues| SUM of ON | SUM of OFF
John | 18          | 140       | 85
Nate | 23          | 110       | 50

Thank you!

Upvotes: 0

Views: 97

Answers (3)

Tim
Tim

Reputation: 5421

Typically one doesn't find two columns, one called ON and the other called OFF, in a table, because they're usually mutually exclusive states and two columns would permit both to be True. Normally all you'd need is one column called ON which can be set to False to indicate the off-state. So what is going on here with your table is not really clear.

If On and Off are indeed mutually exclusive states and you've simply done things in a goofy way, then the best way to get into a result-set rows that are on-state and rows that are off-state is to use a UNION.

           select {desired columns for rows that are on), sum(value) as YourSum
           where on=true
           group by  {group-by column(s)}
           UNION
           select {desired columns for rows that are off}, sum(value) as YourSum
           where off=true
           group by {group-by column(s)}

You could also create two persistent views, one for the On rows and the other for Off Rows and UNION selects against them.

If you want separate columns using the above UNION approach, you can create a dummy column, a subquery, and feed the sums through a bogus aggregate MAX() function:

          select name, max( OnSum) as OnSummed, max( OffSum) as OffSummed from
          (

          select name, sum(value) as OnSum, 0 as OffSum
          from mytable
          where on= true
          group by name
          union
          select name, 0 as OnSum, sum(value) as OffSum
          from mytable
          where off=true
          group by name

          )
          group by name

Upvotes: 0

marc_s
marc_s

Reputation: 755501

Try this:

SELECT
    Name, 
    SUM(issues),
    SUM(CASE [On] WHEN 1 THEN Value ELSE 0 END) 'Sum ON',
    SUM(CASE [On] WHEN 0 THEN Value ELSE 0 END) 'Sum OFF'
FROM 
    dbo.testuser
GROUP BY
    Name

This does give me the desired output you mention.

Upvotes: 2

MartinStettner
MartinStettner

Reputation: 29174

The following should do the trick:

SELECT Users.[NAME], 
       SUM(Users.Issues) As Issues, 
       SUM(CASE WHEN Users.[On] THEN Users.[Value] ELSE 0 END) AS On, 
       SUM(Case WHEN Users.[Off] THEN Users.[Value] ELSE 0 END) As Off 
FROM Users 
GROUP BY Users.[NAME]

Upvotes: 0

Related Questions