How do I get the total sum in the month ?

Question

0.00/5 (No votes)

See more:

Hi everyone,

I wrote this code to get my Sum of expenses that it has been done for one month , but it doesn't work the way I want.

Here is the code :

 SELECT  SUM(Price) AS 'Total' , Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as NOA, Store as 'Store',
  CONVERT(char(7), Date, 120) as 'Month',
  Datepart(Day,date) as 'Day'
FROM [dbo].[ShareCost]
where CONVERT(char(7), Date, 120) =   '2020-08' -- @DateD
GROUP BY Description  , CONVERT(char(7), Date, 120),Store,Datepart(Day,date)
ORDER BY SUM(Price) DESC

and here is the results :

60.36	40	LIDL	2020-08	18
53.80	56	LIDL	2020-08	25
52.55	32	Edeka	2020-08	4
49.74	46	LIDL	2020-08	14
46.37	31	Edeka	2020-08	10
34.35	23	Edeka	2020-08	1
34.00	34	LIDL	2020-08	22
32.84	3	dean & david	2020-08	8
32.36	18	REWE	2020-08	15
27.25	18	Edeka	2020-08	8
25.20	12	Edeka	2020-08	13
22.30	14	Edeka	2020-08	20
13.13	5	Edeka	2020-08	5
10.81	9	Edeka	2020-08	6
9.89	7	Edeka	2020-08	3
5.25	5	Barbarosa	2020-08	2
5.00	7	T Store	2020-08	23
4.90	3	Grotz	2020-08	2
3.90	3	Edeka	2020-08	17

What I need is that the results would be like LIDL and the total sum of all the LIDLs and Numbers in a month and not day.

What I have tried:

I have done some research on google but this case is specific, I tried with TOP 5 Code but still, it gives me the top 5 on the list.

Any help is much appreciated.

Posted 27-Aug-20 2:39am

Member 13410460

Updated 27-Aug-20 3:13am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2020-08-27T03:13:00

Solution 1

GROUP BY doesn't work like that: each time you add a field to the GROUP BY clause, you increase the number of groups you create, not reduce it.
Effectively, what you are saying is

SQL

GROUP BY Column1 THEN BY Column2 THEN BY Column3

In addition, a GROUP BY clause means that you can only return data with includes the GROUP column and aggregate functions such as SUM, MAX, MIN, and so forth.

If you want to get a total plus other non-aggregate info you need to use a JOIN to "combine" a GROUP BY select command with a non-grouped select.

Posted 27-Aug-20 3:13am

OriginalGriff

Comments

Member 13410460 27-Aug-20 9:22am

Thank you for your reply, But in this case, how do I do join?

OriginalGriff 27-Aug-20 9:28am

First think about exactly what you are trying to do, what data comes from where, and what data is aggregated, and which isn't.
Then write the aggregate SELECT and test it.
Then add the rest vai a JOIN.

I can't do that for you - I have no idea what your input data is, or what you actually want!

Member 13410460 27-Aug-20 9:38am

Thank you for your reply, Well I have the sum of Price and the quantity but I cant Sum the STORE which is LIDL and EDEKA since they are NVARCHAR(50).

And what I want is that in the Month 2020-08 I have total Sum of each Store which in this case are LIDL, EDEKA...

Thank you

OriginalGriff 27-Aug-20 10:20am

So the store is the GROUP BY, the select is the SUM, and the HAVING is the month.
What's the problem?

Member 13410460 27-Aug-20 10:39am

Do you mean like this ?
SELECT SUM(Price) AS 'Total', Description as 'Description' , Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as NOA, Store as 'Store',
CONVERT(char(7), Date, 120) as 'Month',
Datepart(Day,date) as 'Day'
FROM [dbo].[ShareCost]
where CONVERT(char(7), Date, 120) ='2020-08' -- @DateD
GROUP BY Store
HAving CONVERT(char(7), Date, 120)
ORDER BY SUM(Price) DESC

OriginalGriff 27-Aug-20 11:01am

No, I mean *do the GROUP BY on it's own first*
When that produces the basic results you need, add in the rest via a JOIN.

The GROUP BY should return only two items: the Stroe and the SUM.

Try it. It's not complicated!

Member 13410460 27-Aug-20 11:39am

SELECT Store as 'Store',Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as NOA,SUM(Price) AS 'Total'
FROM [dbo].[ShareCost]
where CONVERT(char(7), Date, 120) = '2020-08'
GROUP BY Store
ORDER BY SUM(Price) DESC

I used this and it worked, Thank you Mate

OriginalGriff 27-Aug-20 12:08pm

You're welcome!