My Minds Gone Blank - 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)

My Minds Gone Blank

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-06 : 07:51:06

Hi

I've completley forgotten how to do this query.

There are a number of orders that come in each day at different times. I need to do a count of the number of orders that occur a day. So it looks something like the following:

02/09/2008 972
03/09/2008 1094
04/09/2008 985
05/09/2008 697
06/09/2008 177

Any ideas??

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-06 : 07:53:28

SELECT DATEADD(d, DATEDIFF(d, 0, YourDateColumn), 0), COUNT(*)
FROM yourTable
GROUP BY DATEADD(d, DATEDIFF(d, 0, YourDateColumn), 0)

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-06 : 08:04:28

quote:
Originally posted by jamie1984

SELECT
date, COUNT(*) AS Amount
FROM
yourTable
GROUP BY date

If date has time part, it wont work
See Mladen's query

Madhivanan

Failing to plan is Planning to fail

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-11-06 : 11:00:26

Select dt as order_date, count(*) as no_of_orders from (Select distinct Convert(varchar(10,date,104)as Dt from orders) A
Group by Dt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 11:05:09

quote:
Originally posted by danasegarane76

Select dt as order_date, count(*) as no_of_orders from (Select distinct Convert(varchar(10,date,104)as Dt from orders) A
Group by Dt

wont this return only 1 as counts as you're taking distinct inside?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-07 : 00:49:35

quote:
Originally posted by danasegarane76

Select dt as order_date, count(*) as no_of_orders from (Select distinct Convert(varchar(10,date,104)as Dt from orders) A
Group by Dt

How did you think this is correct than other methods posted?

Madhivanan

Failing to plan is Planning to fail

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-07 : 01:34:57

Visakh wht if i use following query to get the same results...(wht are drawbacks)

SELECT CONVERT(VARCHAR,YourDateColumn,111),count(*)
FROM yourTable
GROUP BY CONVERT(VARCHAR,YourDateColumn,111)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-07 : 01:44:22

quote:
Originally posted by lionofdezert

Visakh wht if i use following query to get the same results...(wht are drawbacks)

SELECT CONVERT(VARCHAR,YourDateColumn,111),count(*)
FROM yourTable
GROUP BY CONVERT(VARCHAR,YourDateColumn,111)

Work on dates and not on varchars

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-07 : 01:46:55

quote:
Originally posted by lionofdezert

Visakh wht if i use following query to get the same results...(wht are drawbacks)

SELECT CONVERT(VARCHAR,YourDateColumn,111),count(*)
FROM yourTable
GROUP BY CONVERT(VARCHAR,YourDateColumn,111)

Also see the difference


SELECT 
	CONVERT(VARCHAR,getdate(),111),
	CAST(CONVERT(VARCHAR,getdate(),111) as DATETIME)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 01:50:19

AS Madhi said try not to tamper with datatypes of fields as long as you can. Converting date to varchar makes date manipulations really complex like sorting,duration calaculation etc. so try to group it preserving datatype.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-07 : 02:10:35

THANX madhi and visakh for clearification...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 02:15:34

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources