# Group by issue

• 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

• 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 give you the shirt off *someone else's* back.

• 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.

• 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) BudgetFROM( SELECT....FROM....CASE....WHERE...) aGROUP 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

• 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) BudgetFROM( SELECT....FROM....CASE....WHERE...) aGROUP 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.

• 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?"

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

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