CODEWITHSUNDEEP
sqlsql-servergroup-by
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

GROUP BY for specific rows

I have a sql server result which groups by person. And SUMS's their daily figure and monthly figure.

    Person  Team    Daily Figure    Month To Date
    Tony    Team 1          53              635
    Dan     Team 2         47               172
    Tom     Team 3         17               232
    Simon   Team 2          16              655
    Ben     Team 3         17               232
    Fred    Team 2          16              655

How do i group these results just by team 2? For example i still want the others individual results to show but i want just team 2 to be grouped together.

Example result

   Person   Team    Daily Figure    Month To Date
    Tony    Team 1          53              635
    Tom     Team 3         17               232
    Ben     Team 3         17               232
   Team 2   Team 2         79              1482

Thanks UPDATE:

    SELECT 

     Person = case when Team = 'Team 2' 
     then 'Team 2' else Person  END,
     Team,
     SUM(ISNULL(Figure,0)) AS 'Daily Figure',
     SUM(Month To Date) AS 'Month To Date'

This is my select and it errors

Upvotes: 1

Views: 127

Answers (1)

SqlZim
SqlZim

Reputation: 38023

You can use a case expression for Person both in the select and the group by:

select 
    case when Team = 'Team 2' then 'Team 2' else Person end as Person
  , Team
  , sum([Daily Figure]) as [Daily Figure]
  , sum([Month To Date]) as [Month To Date]
from t
group by
    case when Team = 'Team 2' then 'Team 2' else Person end
  , Team

rextester demo: http://rextester.com/XHQ24032

returns:

+--------+--------+--------------+---------------+
| Person | Team   | Daily Figure | Month To Date |
+--------+--------+--------------+---------------+
| Tony   | Team 1 |           53 |           635 |
| Team 2 | Team 2 |           79 |          1482 |
| Ben    | Team 3 |           17 |           232 |
| Tom    | Team 3 |           17 |           232 |
+--------+--------+--------------+---------------+

If you want to use cross apply() to avoid having to repeat the case expression:

select 
    x.Person
  , t.Team
  , sum(t.[Daily Figure]) as [Daily Figure]
  , sum(t.[Month To Date]) as [Month To Date]
from t
  cross apply (
    select case when t.Team = 'Team 2' then 'Team 2' else t.Person end as Person
    ) as x 
group by
    x.Person
  , t.Team

Or with cross apply(values()):

select 
    x.Person
  , t.Team
  , sum(t.[Daily Figure]) as [Daily Figure]
  , sum(t.[Month To Date]) as [Month To Date]
from t
  cross apply (values 
   (case when t.Team = 'Team 2' then 'Team 2' else t.Person end)
    ) as x (Person)
group by
    x.Person
  , t.Team

Upvotes: 8

Related Questions