Posted by Plamen Ratchev on 03/14/07 05:44

"Spook" <Spook@mailinator.com> wrote in message
news:JzJJh.277$rj1.98@newssvr23.news.prodigy.net...
> I have a SQL table with the following fields:
>
> accounts, orderid's and datetime
>
>
>
> Account OrderID Datetime
> 1 1 2007-03-01 09:30
> 1 2 2007-03-01 09:35
> 10 3 2007-03-01 10:30
> 2 4 2007-03-01 11:30
> 10 5 2007-03-01 12:30
>
>
> Using Query Analyzer, I'd like to run a query where the results are a
> count of orderId's by account on any given day like what I have below:
>
>
>
> Account Orders Date
> 1 2 2007-03-01
> 2 1 2007-03-01
> 10 2 2007-03-01
>

Here is a query to get you this one:

SELECT Account, COUNT(*) AS Orders, CONVERT(CHAR(10), [Datetime], 126) AS
Date
FROM Orders
GROUP BY Account, CONVERT(CHAR(10), [Datetime], 126)

>
> Eventually getting it to this output:
>
> Date TotalOrder
> 2007-03-01 5
>

And here is the next:

SELECT CONVERT(CHAR(10), [Datetime], 126) AS Date, COUNT(*) AS TotalOrders
FROM Orders
GROUP BY CONVERT(CHAR(10), [Datetime], 126)

Regards,

Plamen Ratchev
http://www.SQLStudio.com

• Next in forum: Re: Trying to add a NON-DISTINCT field to a DISTINCT record set in a query.
• Prev in forum: Re: SQL Query help
• Thread view: Re: SQL Query help

[Reply to this message]