Group by - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Group by

Author

Topic

DaveC11
Starting Member

43 Posts

Posted - 2008-05-13 : 06:14:22

Hi,

I have a sales table which contains amongst other things price and datesold. What I want to do is sum the total sold for each month. Is there a way of doing this other than created t-sql script for each month??

At the moment all I've got is this but for each month.

select sum(price) as 'Sales in Jan'
Where datesold between '01-jan-08' and '01-feb-08'

Thanks in advance

Dave

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-13 : 06:17:06

[code]Select Datename(month, datesold) as [month], sum(price) as Sales
from table
where year(datesold) = 2008
group by Datename(month, datesold)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DaveC11
Starting Member

43 Posts

Posted - 2008-05-13 : 06:20:50

Thanks lot that worka treat!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 06:23:53

If there are a lot of records in your table and you have an index over DateSold column, this will perform faster.
It also sorts the records according to month number.

SELECT		DATENAME(MONTH, DateSold) AS theMonth,
		SUM(Price) AS Sales
FROM		Table1
WHERE		DateSold >= '20080101'
		AND DateSold < '20090101'
GROUP BY	DATENAME(MONTH, DateSold),
		DATEPART(MONTH, DateSold)
ORDER BY	DATEPART(MONTH, DateSold)

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-13 : 06:28:52

Peter,

Still the column is wrapped inside the function, wouldn't that prevent use of index?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DaveC11
Starting Member

43 Posts

Posted - 2008-05-13 : 06:38:37

How do I order the table by date using this method? If I try to use the order by clause it just orders the table alphabetically.

Select Datename(month, datesold) as [month], sum(price) as Sales
from table
where year(datesold) = 2008
group by Datename(month, datesold)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 06:46:53

quote:
Originally posted by harsh_athalye

Still the column is wrapped inside the function, wouldn't that prevent use of index?

Good question!
For the main part, the WHERE clause, the index is free to use without any wraps.
The GROUP BY and ORDER BY however is wrapped.

I think we have to make some tests?

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-13 : 06:46:59

Use the ORDER BY clause Peso shown.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 06:47:43

quote:
Originally posted by DaveC11

How do I order the table by date using this method? If I try to use the order by clause it just orders the table alphabetically.

I posted how to 05/13/2008 : 06:23:53, 15 minutes ago.

E 12°55'05.25"
N 56°04'39.16"

DaveC11
Starting Member

43 Posts

Posted - 2008-05-13 : 06:55:46

Thanks,

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-13 : 07:04:44

quote:
Originally posted by Peso

quote:
Originally posted by harsh_athalye

Still the column is wrapped inside the function, wouldn't that prevent use of index?
Good question!
For the main part, the WHERE clause, the index is free to use without any wraps.
The GROUP BY and ORDER BY however is wrapped.

I think we have to make some tests?

E 12°55'05.25"
N 56°04'39.16"

Peter,
Apparently, it seems that it is the WHERE clause that is important to contain SARGable expressions. Since your query contains a sargable WHERE clause, index seek is happening.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 07:40:03

Great

I believe this is happening because of WHERE is treated before both GROUP BY and ORDER BY.

See http://en.wikipedia.org/wiki/Sargable for explanation of SARGable.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-13 : 07:44:44

Yes. I guess you are right.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources