Total sum value out of Group By-CodePudding

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

store
category
sales of particular category in the particular store
total sales of the particular category in all the stores

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?

CodePudding user response：

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;