CODEWITHSUNDEEP
sqlsql-servergroup-by
Николай Беляков
Николай Беляков

Reputation: 183

Total sum value out of Group By

I have a drugstore chain database. In the script below I'm trying to print out

My code:

SELECT 
    s.store_name AS [Drug Store],
    g.group_name AS [Category],
    SUM(f.quantity) AS [Sales pcs.]
    -- (SELECT SUM(quantity) AS [Total sales] GROUP BY g.group_name)
FROM 
    [dbo].[fct_cheque] AS f
INNER JOIN
    [dim_stores] AS s ON s.store_id = f.store_id
INNER JOIN
    dim_goods AS g ON g.good_id = f.good_id 
WHERE
    date_id BETWEEN '20170601' AND '20170630'
GROUP BY
    s.store_name, g.group_name

How to handle the last one?

Upvotes: 1

Views: 73

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

You may use SUM() as an analytic function, for the second sum which is commented out in your question:

SELECT s.store_name AS [Drug Store],
       g.group_name AS [Category],
       SUM(f.quantity) AS [Sales pcs.],
       SUM(SUM(f.quantity)) OVER (PARTITION BY g.group_name) AS [Total sales]
FROM [dbo].[fct_cheque] AS f
INNER JOIN [dim_stores] AS s ON s.store_id = f.store_id
INNER JOIN dim_goods AS g ON g.good_id = f.good_id
WHERE date_id BETWEEN '20170601' AND '20170630'
GROUP BY s.store_name, g.group_name;

Upvotes: 2

Related Questions