CODEWITHSUNDEEP
sqlsql-server
Nonagon
Nonagon

Reputation: 407

A running summary of totals in SQL Server

Come up against an issue where I want to summarize results in a query.

Example as follows:

NAME | FRUIT | PRICE 
-----+-------+------
JOHN | APPLE |  2
JOHN | APPLE |  2
JOHN | APPLE |  2
JOHN | APPLE |  2
DAVE | GRAPE |  3
DAVE | GRAPE |  3
DAVE | GRAPE |  3

This is my table at the moment, what i need though is to have a summary of Johns business, like below:

NAME | FRUIT | PRICE 
-----+-------+------
JOHN | APPLE |  2
JOHN | APPLE |  2
JOHN | APPLE |  2
JOHN | APPLE |  2
JOHN | TOTAL |  8 
DAVE | GRAPE |  3
DAVE | GRAPE |  3
DAVE | GRAPE |  3
DAVE | TOTAL |  9

I have tried to group the information but it does not reflect what i want, plus if John were to have different fruit it would need to sum that up before it sums up the next part and it needs to have a running total for all values in the NAME field as there will be a number of customers.

Any advice would be great

EDIT

I have tried using Rollup but I keep getting totals of all values in a seperate column where as I would like to see it as the way it is formatted above

Upvotes: 2

Views: 103

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

The solution to your problem is GROUPING SETS. However, your rows are not unique. Alas, so this adds a unique value, just so you can keep your original rows:

with t as (
      select t.*, row_number() over (order by (select null)) as seqnum
      from t
     )
select name, ,
       coalesce(fruit, 'Total') as fruit,
       sum(price) as price
from t
group by grouping sets ( (name, fruit, seqnum), (name) )
order by name,
      (case when fruit is not null then 1 else 2 end);

Upvotes: 0

GandRalph
GandRalph

Reputation: 635

This will get you a running total per customer per fruit:

create table #Sales([Name] varchar(20), Fruit varchar(20), Price int)
insert into #Sales([Name], Fruit, Price)
values
('JOHN','APPLE',2),
('JOHN','APPLE',2),
('JOHN','APPLE',2),
('JOHN','APPLE',2),
('DAVE','GRAPE',3),
('DAVE','GRAPE',3),
('DAVE','GRAPE',3)


Select c.*
, SUM(Price) OVER (PARTITION BY c.[Name], c.[Fruit] ORDER BY c.[Name], c.[Fruit] rows between unbounded preceding and current ROW ) as RunningTotal
from #Sales c
order by c.[Name], c.[Fruit] asc

drop table #Sales

Output:

enter image description here

Upvotes: 1

EzLo
EzLo

Reputation: 14189

A solution with UNION and GROUP BY.

;WITH PricesWithTotals AS
(
    SELECT
        Name,
        Fruit,
        Price
    FROM
        YourTable
    UNION ALL
    SELECT
        Name,
        Fruit = 'TOTAL',
        Price = SUM(Price)
    FROM
        YourTable
    GROUP BY
        Name
)
SELECT
    Name,
    Fruit,
    Price
FROM
    PricesWithTotals
ORDER BY
    Name,
    CASE WHEN Fruit <> 'Total' THEN 1 ELSE 999 END ASC,
    Fruit

Upvotes: 3

Related Questions