CODEWITHSUNDEEP
sql-servert-sql
Bhushan
Bhushan

Reputation: 134

Subtotals based on specific columns in SQL Server 2012

Could anyone suggest the query for the below scenario?

Table is as below

MachineName ManufacturedBy Amount
---------------------------------
A              X            50
B              X            50
C              Q            30
D              Q            30

The data should be as follows

  MachineName ManufacturedBy Amount
  ----------------------------------
    A              X            50
    B              X            50
    Subtotal                   100

    C              Q            30
    D              Q            30
    Subtotal                    60

    Grandtotal

Thanks

Sasi

Upvotes: 0

Views: 72

Answers (2)

Bhushan
Bhushan

Reputation: 134

I tried the below. It also worked for me.

SELECT ManufacturedBy , MachineName , SUM AS Amount FROM yourTable GROUP BY GROUPING SETS (ManufacturedBy,(ManufacturedBy, MachineName),());

Thanks

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

You may use GROUP BY with ROLLUP:

SELECT
    COALESCE(ManufacturedBy, 'All Manufacturers') AS ManufacturedBy,
    COALESCE(MachineName, 'All Machines') AS MachineName,
    SUM(Amount) AS Amount
FROM yourTable
GROUP BY ROLLUP (ManufacturedBy, MachineName);

Demo

Upvotes: 3

Related Questions