SQL Server GROUP BY multiple columns

Question

Let's assume I have in SQL Server the following table with only seven days available (SUN - SAT): Orders

| Day | ProductType | Price |
| SUN | 1           | 10    |
| MON | 1           | 15    |
| MON | 2           | 20    |
| MON | 3           | 10    |
| TUE | 1           | 5     |
| TUE | 3           | 5     |
...

I need to group the data in a way so that to see the Total sum of Prices by each distinct Day and two groups of ProductType (= 1 and > 1):

| Day | FirstProductTypeTotal | RestProductsTypesTotal | GrandTotal |
| SUN | 10                    | 0                      | 10         |
| MON | 15                    | 30                     | 45         |
| TUE | 5                     | 5                      | 10         |
...

where FirstProductTypeTotal is ProductType = 1 and RestProductTypesTotal is ProductType > 1.

Is it possible to select this in one select instead of writing two different selects:

Select Day, SUM(Price) as FirstTotal from Orders where ProductType = 1 group by Day

and

Select Day, SUM(Price) as SecondTotal from Orders where ProductType > 1 group by Day

And then add FirstTotal and SecondTotal manually in the code to get the Grand total for each day of the week?

You can use case statement.

Josan
– Josan

2016-09-02 15:12:16 +00:00
Commented Sep 2, 2016 at 15:12 — Josan
– Josan, Commented Sep 2, 2016 at 15:12

Jaydip Jadhav · Accepted Answer · 2016-09-02 15:22:51Z

2

Use CASE Expression

Select Day, SUM(CASE WHEN ProductType = 1 THE Price ELSE 0 END)  AS FirstTotal,
            SUM(CASE WHEN ProductType > 1 THE Price ELSE 0 END) AS SecondTotal,
            SUM(Price) AS GrandTotal
FROM Orders 
group by Day

edited Sep 2, 2016 at 15:22

answered Sep 2, 2016 at 15:11

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BohdanZPM Over a year ago

Thanks a lot! That helped.

Rich Benner · Accepted Answer · 2016-09-02 15:25:22Z

0

Try conditional aggregation;

Sample data;

CREATE TABLE #Orders ([Day] varchar(10), ProductType int, Price int)
INSERT INTO #Orders ([Day],ProductType, Price)
VALUES
 ('SUN',1,10)
,('MON',1,15)
,('MON',2,20)
,('MON',3,10)
,('TUE',1,5)
,('TUE',3,5)

Query;

SELECT
    o.[Day]
    ,SUM(CASE WHEN o.ProductType = 1 THEN o.Price ELSE 0 END) FirstTotal
    ,SUM(CASE WHEN o.ProductType > 1 THEN o.Price ELSE 0 END) SecondTotal
    ,SUM(o.Price) GrandTotal
FROM #Orders o
GROUP BY o.[Day]

Result

Day FirstTotal  SecondTotal GrandTotal
MON 15          30          45
SUN 10          0           10
TUE 5           5           10

You'd just need to sort out the ordering of the days because SQL Server by definition doesn't store the data in any particular order.

edited Sep 2, 2016 at 15:25

answered Sep 2, 2016 at 15:12

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

Collectives™ on Stack Overflow

SQL Server GROUP BY multiple columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related