Tried everything - giving up on simple query

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

Tried everything - giving up on simple query

Author

Topic

inq_ah
Starting Member

3 Posts

Posted - 2011-09-11 : 12:13:58

Hi

Well maybe it is not a simple query but i am pretty much giving up now.
I have the following table - "order_transactions":

Order_type_id Order_Date
1001 8/24/2011
1004 8/24/2011
1003 8/11/2011
1004 8/24/2011
1001 8/11/2011
1002 8/22/2011
1002 8/22/2011
1004 8/15/2011

I need to count how many order_types for a specific date were there:
Basically expecting this:
Order_Date Order_type_id Count_Of_Order_Types_Per_Date
8/11/2011 1001 1
8/11/2011 1003 1
8/15/2011 1004 1
8/22/2011 1002 2
8/24/2011 1001 1
8/24/2011 1004 2

I really appreciate any insight.
Thank you,
Alex

Alex Melamed

singularity
Posting Yak Master

153 Posts

Posted - 2011-09-11 : 12:19:22

[code]
select order_date, order_type_id, count(*) as count_of_order_types_per_date
from order_transactions
group by order_date, order_type_id
order by order_date, order_type_id
[/code]

inq_ah
Starting Member

3 Posts

Posted - 2011-09-11 : 12:28:03

Thank you very much!
I think i even tried this before - but now i see where my mistake is - I really appreciate - THANK YOU SO MUCH!!!!!

I now will need to somehow truncate (I am on SQL Server) - the actual order_date, because it counts each date as distinct due to the date/time format:
8/23/2011 11:07 1001 1
8/23/2011 14:09 1001 1
8/23/2011 14:11 1001 1

And in my case i just need:
8/23/2011 1001 1

Have a Great Date!
Alex

quote:

Originally posted by singularity


select order_date, order_type_id, count(*) as count_of_order_types_per_date
from order_transactions
group by order_date, order_type_id
order by order_date, order_type_id

Alex Melamed

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-11 : 12:42:50

http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-11 : 12:59:11

[code]
select dateadd(dd,datediff(dd,0,order_date),0) as OrderDtWOTime, order_type_id, count(*) as count_of_order_types_per_date
from order_transactions
group by dateadd(dd,datediff(dd,0,order_date),0), order_type_id
order by OrderDtWOTime, order_type_id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inq_ah
Starting Member

3 Posts

Posted - 2011-09-11 : 13:28:33

THANK YOU ALL!

SQL folks - you are very very helpful!

I hope i can do the same one day and help others...

Alex
P.S. I promise i will also always help with my new skills as i learn more and more..

quote:
Originally posted by visakh16

select dateadd(dd,datediff(dd,0,order_date),0) as OrderDtWOTime, order_type_id, count(*) as count_of_order_types_per_date
from order_transactions
group by dateadd(dd,datediff(dd,0,order_date),0), order_type_id
order by OrderDtWOTime, order_type_id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Alex Melamed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-11 : 13:37:17

welcome

you sure can shortly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources