How to GROUP BY one column and also summing up the result for this grouped column

Question

I have such a situation, I have a table where for some reason a column called STORE_NUMBER gets repeated in the actual result multiple times so the result goes by item level and not a sum of results for a store_number.

Here is my code. Does anyone know how I can adjust the code (group it by a store) so that I have 1 line for STORE_NUMBER 1 and 1 line for STORE_NUMBER 2 etc. and also summing all the results for each specific store?

SELECT s.DEAGY AS 'AGENCY', l.cmschn AS 'MASTER_CHAIN', s.DECHN AS 'CHAIN', s.DESTR AS 'STORE_NUMBER', s.DEWEDT AS 'WK_ENDING',  

CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY ELSE 0 END AS  'OB_UNITS', 

CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN -1 * sm.DEQTY ELSE 0 END AS 'RET_UNITS',

CAST (( CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY ELSE '0' END) AS int) 
- 
CAST ( ( CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN -1 * sm.DEQTY ELSE '0' END )   AS int) AS 'NET_UNITS',

CAST (( CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN -1 * sm.DEQTY ELSE 0 END) AS Money) 
/ 
CAST ( ( CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY ELSE 0 END )  AS Money) 
* 
CAST (100 AS Money) AS '%RET_UNITS',

CASE
WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERSP ELSE '0'
END AS OB_BILLING,
CASE
WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM'  THEN (-1 * SM.DEQTY) * sm.DERSP ELSE 0
END AS RET_BILLING,
CAST ( ( CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERSP ELSE '0' END) 
     AS MONEY) 
- 
CAST ( ( CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN (-1 * sm.DEQTY) * sm.DERSP ELSE '0' END )   AS MONEY) AS 'NET_BILLING',

CAST (( CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM'  THEN (-1 * SM.DEQTY) * sm.DERSP ELSE 0
END) AS Money) 
/ 
CAST ( ( CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERSP ELSE '0' END)  AS Money) 
* 
CAST (100 AS Money) AS '%RET_BILLING',




CASE 
WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERPRC ELSE 0
END AS OB_MSRP, 
CASE
WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN ( -1 * sm.DEQTY) * sm.DERPRC ELSE 0
END AS RET_MSRP,
CAST ( ( CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERPRC ELSE '0' END) AS MONEY) 
- 
CAST ( ( CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN (-1 * sm.DEQTY) * sm.DERPRC ELSE '0' END )   AS MONEY) AS 'NET_MSRP',





CAST (( CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN ( -1 * sm.DEQTY) * sm.DERPRC ELSE 0 END) AS Money) 
/ 
CAST ( ( CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERPRC ELSE 0
END )  AS Money) 
* 
CAST (100 AS int) AS '%RET_MSRP'




INTO ##temp6
FROM REPIT.SMPDTA.SMPDTLM s
JOIN REPIT.LEVYDTA.LDSCHNM l
ON s.DEAGY=l.CMAGY AND s.DECHN=l.CMCHN
JOIN LEVYDTA.SMPRTNM sm
ON s.DEAGY=sm.DEAGY AND s.DECHN=sm.DECHN AND s.DESTR=sm.DESTR AND s.DEWEDT=sm.DEWEDT
where s.DEWEDT = 20210807 AND s.DEAGY=10 AND s.DECHN='WM';

Most people here want sample table data and the expected result as formatted text, not as images. (And I'm too old to read that tiny image text...) — jarlh, Aug 09 '21 at 18:45
Does this answer your question? [SQL group by "column name"](https://stackoverflow.com/questions/19776928/sql-group-by-column-name) — ru4ert, Aug 09 '21 at 18:48
@ru4ert no it doesn't help. I immediately get an error "Msg 8120, Level 16, State 1, Line 4 Column 'REPIT.SMPDTA.SMPDTLM.DEAGY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." — DIFF1, Aug 09 '21 at 18:53
can you explain me "result goes by item level and not a sum of results for a store_number"? cz i dont get it. I just see in your code PRE_DATA for final SUM/AVG and etc if i get it rigth. How should we group? What do you want to see — letronas, Aug 09 '21 at 19:18
@letronas I have 2 pictures. 1 picture - is what I have right now. You see the store 1946 is repeated multiple times, I want to have only one unique row 1946 with unique count of all other columns. For Example for this store 1946 I need OB_UNITS to be calculated 2+2+2.... until i have a sum of all units for the store 1946. Same goes to RET_UNITS etc all the columns with returned units for the store 1946 should be summed up and not by a line item (like 2 in one column, 2 in another column, 2 in the third column etc). — DIFF1, Aug 09 '21 at 19:40
@letronas all of them starting from OB_UNITS. All OB_UNITS should be summed for a specific store (for example for store 1946, then for store 1947 etc). After that the same sum ups should be for RET_UNITS etc. — DIFF1, Aug 09 '21 at 19:44

score 0 · Accepted Answer · answered Aug 09 '21 at 19:53

I don't think that all you need is sum, sometimes there should be average as i guess.

I see your task like this (i write code just in notepad++ that's why check the mistakes)

WITH PRE_DATA AS(
SELECT 
s.DEAGY AS 'AGENCY', 
l.cmschn AS 'MASTER_CHAIN',
s.DECHN AS 'CHAIN', 
s.DESTR AS 'STORE_NUMBER', 
s.DEWEDT AS 'WK_ENDING',  
CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY ELSE 0 END AS  'OB_UNITS', 
CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN -1 * sm.DEQTY ELSE 0 END AS 'RET_UNITS',
CAST (( CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY ELSE '0' END) AS int) 
- 
CAST ( ( CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN -1 * sm.DEQTY ELSE '0' END )   AS int) AS 'NET_UNITS',

CAST (( CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN -1 * sm.DEQTY ELSE 0 END) AS Money) 
/ 
CAST ( ( CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY ELSE 0 END )  AS Money) 
* 
CAST (100 AS Money) AS '%RET_UNITS',

CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERSP ELSE '0'END AS OB_BILLING,
CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM'  THEN (-1 * SM.DEQTY) * sm.DERSP ELSE 0 END AS RET_BILLING,
CAST (( CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERSP ELSE '0' END) AS MONEY) 
- 
CAST ( ( CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN (-1 * sm.DEQTY) * sm.DERSP ELSE '0' END )AS MONEY) AS 'NET_BILLING',

CAST (( CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM'  THEN (-1 * SM.DEQTY) * sm.DERSP ELSE 0
END) AS Money) 
/ 
CAST ( ( CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERSP ELSE '0' END)  AS Money) 
* 
CAST (100 AS Money) AS '%RET_BILLING',

CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERPRC ELSE 0 END AS OB_MSRP, 
CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN ( -1 * sm.DEQTY) * sm.DERPRC ELSE 0 END AS RET_MSRP,
CAST ( ( CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERPRC ELSE '0' END) AS MONEY) 
- 
CAST ( ( CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN (-1 * sm.DEQTY) * sm.DERPRC ELSE '0' END )   AS MONEY) AS 'NET_MSRP',

CAST (( CASE WHEN sm.DEIO='I' AND sm.DEAGY = 10 AND sm.DECHN = 'WM' THEN ( -1 * sm.DEQTY) * sm.DERPRC ELSE 0 END) AS Money) 
/ 
CAST ( ( CASE WHEN s.DEIO='O' AND s.DEAGY = 10 AND s.DECHN = 'WM' THEN s.DEQTY * s.DERPRC ELSE 0
END )  AS Money) 
* 
CAST (100 AS int) AS '%RET_MSRP'

FROM REPIT.SMPDTA.SMPDTLM s
INNER JOIN REPIT.LEVYDTA.LDSCHNM l ON s.DEAGY=l.CMAGY AND s.DECHN=l.CMCHN
INNER JOIN LEVYDTA.SMPRTNM sm ON s.DEAGY=sm.DEAGY AND s.DECHN=sm.DECHN AND s.DESTR=sm.DESTR AND s.DEWEDT=sm.DEWEDT
WHERE s.DEWEDT = 20210807 AND s.DEAGY=10 AND s.DECHN='WM';
)

SELECT AGENCY, MASTER_CHAIN, CHAIN, STORE_NUMBER, WK_ENDING,
SUM(OB_UNITS) as OB_UNITS,
SUM(RET_UNITS) as RET_UNITS,
SUM(NET_UNITS) as NET_UNITS,
AVG([%RET_UNITS]) as '%RET_UNITS',
SUM(OB_BILLING) as OB_BILLING,
SUM(RET_BILLING) as RET_BILLING,
SUM(NET_BILLING) as NET_BILLING,
AVG([%RET_BILLING]) as '%RET_BILLING',
SUM(OB_MSRP) as OB_MSRP,
SUM(RET_MSRP) as RET_MSRP,
SUM(NET_MSRP) as NET_MSRP,
AVG([%RET_MSRP]) as '%RET_MSRP'
FROM PRE_DATA
GROUP BY AGENCY, MASTER_CHAIN, CHAIN, STORE_NUMBER, WK_ENDING

I hope that it help you

Syntax might be a little bit different, but you should get the main idea or key in what i'm thinking — letronas, Aug 09 '21 at 19:54

How to GROUP BY one column and also summing up the result for this grouped column

1 Answers1