Group by issue

Question

Post reply

Group by issue

pedroccamara

SSC-Addicted

Points: 431
More actions
February 6, 2023 at 2:47 pm

#4146089

Hello everyone!!
I have this SQL statement:
SELECT
dateadd(year,(2023 - year(DH.DOC_DATE)),DH.DOC_DATE) AS 'Date',
DD.STORE_CODE AS Store,
CONCAT(DD.STORE_CODE,'/',DD.PROCESS) AS PROCESS,
DH.LOCAL AS Local,
DD.PRODUCT_CODE AS 'Cod Prod',
(CASE
WHEN MONTH(DH.DOC_DATE)=1 and YEAR(DH.DOC_DATE)=2022 then (PRICE_BEFORE_TAX*QNT)*1.05
WHEN MONTH(DH.DOC_DATE)=2 and YEAR(DH.DOC_DATE)=2022 then (PRICE_BEFORE_TAX*QNT)*1.05
WHEN MONTH(DH.DOC_DATE)=3 and YEAR(DH.DOC_DATE)=2022 then (PRICE_BEFORE_TAX*QNT)*1.05
WHEN MONTH(DH.DOC_DATE)=4 and YEAR(DH.DOC_DATE)=2022 then (PRICE_BEFORE_TAX*QNT)*1.05
WHEN MONTH(DH.DOC_DATE)=5 and YEAR(DH.DOC_DATE)=2022 then (PRICE_BEFORE_TAX*QNT)*1.05
WHEN MONTH(DH.DOC_DATE)=6 and YEAR(DH.DOC_DATE)=2022 then (PRICE_BEFORE_TAX*QNT)*1.05
WHEN MONTH(DH.DOC_DATE)=7 and YEAR(DH.DOC_DATE)=2022 then (PRICE_BEFORE_TAX*QNT)*1.05
WHEN MONTH(DH.DOC_DATE)=8 and YEAR(DH.DOC_DATE)=2022 then (PRICE_BEFORE_TAX*QNT)*1.05
WHEN MONTH(DH.DOC_DATE)=9 and YEAR(DH.DOC_DATE)=2022 then (PRICE_BEFORE_TAX*QNT)*1.05
WHEN MONTH(DH.DOC_DATE)=10 and YEAR(DH.DOC_DATE)=2022 then (PRICE_BEFORE_TAX*QNT)*1.05
WHEN MONTH(DH.DOC_DATE)=11 and YEAR(DH.DOC_DATE)=2022 then (PRICE_BEFORE_TAX*QNT)*1.05
WHEN MONTH(DH.DOC_DATE)=12 and YEAR(DH.DOC_DATE)=2022 then (PRICE_BEFORE_TAX*QNT)*1.05
END) AS Budget
FROM dbo.FDOCDETAIL_INFO DI
RIGHT OUTER JOIN dbo.FDOCDETAIL DD ON DI.STORE_CODE = DD.STORE_CODE AND DI.PROCESS = DD.PROCESS AND DI.SEQ_NUMBER = DD.SEQ_NUMBER
INNER JOIN FDOCHEADER DH ON DD.STORE_CODE = DH.STORE_CODE AND DD.PROCESS = DH.PROCESS LEFT OUTER JOIN FDOCCONFIG CF ON DH.DOC_FOTYPE = CF.CODE
WHERE (DH.CANCELED <> 1)
AND DH.DOC_DATE BETWEEN (cast(year(getdate())-1 as varchar) + '-01-01') AND cast(year(getdate())-1 as varchar) + '-12-31'
AND DH.DOC_TYPE IN ('V', 'R')
AND CF.SAFT_DOC_TYPE <> 'null'
and now I would like to group these values like so:
GROUP BY DH.DOC_DATE, DD.STORE_CODE, DH.LOCAL, DD.PROCESS, DD.PRODUCT_CODE
the error is "Column 'dbo.FDOCDETAIL_INFO.PRICE_BEFORE_TAX' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 9" ...up to line 20
This is a budget table 2023 I built from the sales table of 2022.
This is what I get before grouping it.
Now the big question: how do I group it?
Thank you all in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100942 More actions · Answer 1

Once again, unusable data. We need CREATE TABLE and INSERT statement(s), not a picture/"splat" of data on a screen.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 2

And please post SQL code in a code block.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

pedroccamara SSC-Addicted Points: 431 More actions · Answer 3

Hello @scottpletcher and @SSC Guru

I am really sorry to say but don't even know how to do it and also, I really thought that the way I've explained, it would be easy to advise. Could it be the code icon we have here SSC Guru?

Please take a minute to explain what I should have done instead.

About the issue, I remember the lessons about sub queries and I've done this:

SELECT Date, Store, Local, PROCESS, CodProd, SUM(Budget) Budget
FROM( SELECT....FROM....CASE....WHERE...) a
GROUP BY Date, Store, Local, PROCESS, CodProd

I've already test it and it seems working. May I have your opinions?

Thanks a lot you both

Phil Parkin SSC Guru Points: 246686 More actions · Answer 4

pedroccamara wrote:

Hello @scottpletcher and @SSC Guru I am really sorry to say but don't even know how to do it and also, I really thought that the way I've explained, it would be easy to advise. Could it be the code icon we have here SSC Guru? Please take a minute to explain what I should have done instead.
About the issue, I remember the lessons about sub queries and I've done this:
SELECT Date, Store, Local, PROCESS, CodProd, SUM(Budget) Budget
FROM( SELECT....FROM....CASE....WHERE...) a
GROUP BY Date, Store, Local, PROCESS, CodProd
I've already test it and it seems working. May I have your opinions?
Thanks a lot you both

I've explained in a previous post how to post data.

Click on the Code icon while you are posting and paste your code inside the window which appears, then click OK to insert the formatted code into your post.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

pedroccamara SSC-Addicted Points: 431 More actions · Answer 5

Got it Phil. Next time I won't forget to do it.

And, about the issue itself, my question ...

"I've already test it and it seems working. May I have your opinions?"