We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Select Query - Total Orders Per Day / Month

Medium Priority
1,090 Views
Last Modified: 2012-05-06
Assuming I have a table of orders and a date range, how might I go about writing a select query to return just the total number of orders per day / month within that date range?

Order Table
OrderID, DateOrdered, Value, Customer Name (etc)

Ideal Query Results
Day 1 - Total Value - Number of Orders
Day 2 - Total Value - Number of Orders
...

or similar for months rather than days.  Is this possible just using TSQL or will I need to use some sort of reporting addon?
Comment
Watch Question

Commented:
It's possible...

Something like below should set you off in the right direction:
SELECT
	CONVERT(Varchar(11), DateOrdered, 103)
	,COUNT(OrderID)
FROM
	X
ORDER BY
	DateOrdered ASC

Open in new window

CERTIFIED EXPERT
Commented:
per day
select Convert(varchar(20),DateOrdered),101) , count(*)
From ORDER_TABLE
Group by Convert(varchar(20),DateOrdered),101)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

Commented:
per day
select Convert(varchar(20),DateOrdered,101) , count(*)
From ORDER_TABLE
Group by Convert(varchar(20),DateOrdered,101)
CERTIFIED EXPERT

Commented:
per month
select  Month(DateOrdered) ,Year(DateOrdered), count(*)
From ORDER_TABLE
Group by  Month(DateOrdered) ,Year(DateOrdered)

Author

Commented:
Thanks.  Will award to pratima as the examples are exactly what I need without modification.

Noted that CAST(FLOOR(CAST(Datecolumn AS FLOAT)) AS DATETIME) seems to work much faster than converting to varchar.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
well, here is my two cents worth - pretty much the same as above, just with all the bits you asked for.... Including an order by to make sure it shows in desired datetime sequence (which should do anyway).

select convert(varchar,orderdate,106),  count(distinct orders) as number_of_orders, sum(Value) as daily_order_value
from orders
group by convert(varchar,orderdate,106)
order by convert(datetime,convert(varchar,orderdate,106))
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
didn't realise how long I had this open for... sorry about that.

also, don't use float, it might appear to be quicker mainly because it retains datetime construct, but use : select CAST(floor(CAST(getdate() AS decimal(18,6))) AS DATETIME)   float is an approximation and decimal is an absolute numeric.

Author

Commented:
Thanks for the decimal comment.  Interesting.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.