Combine multiple SQL queries in 1

Question

I kinda got stuck at trying to combine a few queries. What I got is the table holding sort of statistics and reference to another table.

to get statistics report I'm running (short version):

SELECT COUNT(id) 
from [Actions] 
where date between '2012-01-01 00:00:00' AND '2012-01-01 23:59:59' 
  AND [Action]='request'

The question is if I want to get every-day statistics during the specified time period, how should this query look like? I understand if I change start and end dates, I'll get statistics for the whole specified period and not grouped by day. What am I missing?

Mahmoud Gamal · Accepted Answer · 2012-11-03 05:11:57Z

3

Just GROUP BY date after eleminating the time part like so:

SELECT 
  CONVERT(VARCHAR(10), [date], 121) ByDay, COUNT(id) 
FROM [Actions] 
WHERE date BETWEEN '2012-01-01 00:00:00' AND '2012-01-01 23:59:59' 
  AND [Action]='request'
GROUP BY CONVERT(VARCHAR(10), [date], 121)

answered Nov 3, 2012 at 5:11

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex D Over a year ago

I knew I was missing something! Thanks, but I'm guessing the right form will be to use 101 and not 121 to prevent the query to group records by minutes and seconds. am I right?

Mahmoud Gamal Over a year ago

@AlexD - I think the two will work for you. 121 will work since it will get only the first 10 character by varchar(10) which are the date part only.

Alex D Over a year ago

Anyways, thanks so much for reminding me how does GROUP BY works!

Collectives™ on Stack Overflow

Combine multiple SQL queries in 1

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related