Sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum

Author

Topic

mukhan85
Starting Member

46 Posts

Posted - 2008-09-09 : 11:19:40

Hi, I have the following table:
Name Date Color
Apple 4/5/05 Red
Orange 3/4/08 Yellow
...

I used the query:
that generated the following result set:

Name Number Month
Apple 45 1
Orange 33 1
Peach 222 2

I am trying to get the same result set, but I need one more column that would display the total number of fruits for the given month.

Name Number Month Total
Apple 45 1 78
Orange 33 1 78
Peach 22 2 22
...
I tried to put a subquery inside my query but, it gave an error that more than one result was returned.

Thank you.
Thank you.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-09-09 : 11:26:47

Didnt see a question in your post. Post sample data, table structure, desired output and problems you are facing. Help us help you.

mukhan85
Starting Member

46 Posts

Posted - 2008-09-09 : 11:34:53

quote:
Originally posted by sakets_2000

Didnt see a question in your post. Post sample data, table structure, desired output and problems you are facing. Help us help you.

Hi,
well I have the following results from my query:
Name Number Month
Apple 45 1
Orange 33 1
Peach 22 2

I am trying to get the same result set, but I need one more column that would display the total number of fruits for the given month.
For example, for Month 1 there were 45 apples and 33 Oranges. So, the column Total should show 45+33 = 78 for all fruits for Month 1 and so on.
Name Number Month Total
Apple 45 1 78
Orange 33 1 78
Peach 22 2 22

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 11:35:37

What is Number value? Is it number of records for that name?
What is Month value? Is it the month number of the date column?
What is Total value?

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-09-09 : 11:42:59

quote:
Originally posted by mukhan85

quote:
Originally posted by sakets_2000

Didnt see a question in your post. Post sample data, table structure, desired output and problems you are facing. Help us help you.

Hi,
well I have the following results from my query:
Name Number Month
Apple 45 1
Orange 33 1
Peach 22 2

I am trying to get the same result set, but I need one more column that would display the total number of fruits for the given month.
For example, for Month 1 there were 45 apples and 33 Oranges. So, the column Total should show 45+33 = 78 for all fruits for Month 1 and so on.
Name Number Month Total
Apple 45 1 78
Orange 33 1 78
Peach 22 2 22

Thank you.

select * from yourtable join
(select month,sum(number) as total from yourtable group by month)z
on z.[month]=test.month

mukhan85
Starting Member

46 Posts

Posted - 2008-09-09 : 11:43:17

quote:
Originally posted by Peso

What is Number value? Is it number of records for that name?
What is Month value? Is it the month number of the date column?
What is Total value?

E 12°55'05.63"
N 56°04'39.26"

Well the actual table looks like this:
Name Date_Sold Address
Apple 4/5/08 ...
Apple 4/3/05 ...

So, I am trying to calculate how many apples where sold every month.
So the Number column displays number of apples sold in the indicated month in the Month column. and Total shows the total number of items sold for the given month (Includes all fruits: apple, orange...)

Thank you.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-09-09 : 11:43:52

select * from yourtable join
(select month,sum(number) as total from yourtable group by month)z
on z.[month]=yourtable.month

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 11:48:59

Is month calculated out of date column?

E 12°55'05.63"
N 56°04'39.26"

mukhan85
Starting Member

46 Posts

Posted - 2008-09-09 : 11:52:15

quote:
Originally posted by Peso

Is month calculated out of date column?

E 12°55'05.63"
N 56°04'39.26"

Yep

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 12:01:37

[code]SELECT DISTINCT Name,
COUNT(*) OVER (PARTITION BY DATEPART(MONTH, Date)) AS Number
DATEPART(MONTH, Date) AS [Month],
COUNT(*) OVER (PARTITION BY Name) AS Total
FROM Table1
ORDER BY Name,
DATEPART(MONTH, Date)[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources