SQLTeam.com | Weblogs | Forums

Query with subqueries using queries results

sql2008r2

#1

I have a table

ID Event Date
1 0 05/01/2016 15:05
2 0 05/01/2016 15:07
3 1 05/01/2016 15:07
4 1 05/01/2016 15:07
5 0 05/01/2016 18:08
6 1 05/01/2016 18:09
7 1 05/01/2016 18:09
8 1 05/01/2016 18:09
9 0 05/01/2016 23:16
10 3 05/01/2016 23:18
11 3 06/01/2016 01:07
12 3 06/01/2016 07:24
13 3 06/01/2016 09:24
14 0 06/01/2016 09:39
15 1 06/01/2016 09:40
16 1 06/01/2016 09:41
17 3 06/01/2016 10:26
18 3 06/01/2016 10:27
19 0 06/01/2016 10:29
20 1 06/01/2016 10:29
21 1 06/01/2016 10:29
22 0 06/01/2016 12:27
23 1 06/01/2016 12:28
24 1 06/01/2016 12:28
25 3 06/01/2016 13:49
26 3 06/01/2016 15:11
27 3 07/01/2016 08:22
28 3 07/01/2016 08:54
29 0 07/01/2016 09:47
30 1 07/01/2016 09:48
31 1 07/01/2016 09:50
32 3 07/01/2016 11:29
33 3 07/01/2016 11:35
34 0 07/01/2016 11:59
35 1 07/01/2016 12:00
36 1 07/01/2016 12:01
37 0 07/01/2016 12:13
38 2 07/01/2016 12:17
49 2 07/01/2016 13:08
50 3 07/01/2016 15:01
51 0 07/01/2016 16:52
52 1 07/01/2016 16:52
53 1 07/01/2016 16:53
54 0 07/01/2016 17:44
55 1 07/01/2016 17:45
56 1 07/01/2016 17:45
57 3 07/01/2016 20:22
58 3 07/01/2016 20:36
59 3 08/01/2016 01:06
60 3 08/01/2016 07:28
61 3 08/01/2016 09:38
62 3 08/01/2016 11:23
63 3 08/01/2016 11:23
64 3 08/01/2016 11:29
65 3 08/01/2016 11:30
66 3 08/01/2016 11:39
67 3 08/01/2016 11:53
68 3 08/01/2016 13:58
69 3 08/01/2016 14:28
70 3 08/01/2016 16:16
188 3 19/01/2016 14:02
189 3 19/01/2016 18:20
190 3 19/01/2016 21:27
191 0 20/01/2016 08:38
192 1 20/01/2016 08:38
193 1 20/01/2016 08:39
194 0 20/01/2016 09:40
195 3 20/01/2016 09:41
196 0 20/01/2016 13:36
197 1 20/01/2016 13:52
198 1 20/01/2016 13:52
199 3 20/01/2016 15:40
200 3 21/01/2016 08:04

A want to show the results grouped by Date only and then each action type as a column for that date, for example

Date Event_0 Event_1 Event_2 Event_3
07/01/2016 1 3 0 4
06/01/2016 2 4 3 1
01/01/2016 10 3 4 5


#2
SELECT 
	Date,
	SUM(CASE WHEN [Event] = 0 THEN 1 ELSE 0 END) AS Event_0,
	SUM(CASE WHEN [Event] = 1 THEN 1 ELSE 0 END) AS Event_1,
	SUM(CASE WHEN [Event] = 2 THEN 1 ELSE 0 END) AS Event_2,
	SUM(CASE WHEN [Event] = 3 THEN 1 ELSE 0 END) AS Event_3
FROM
	(SELECT DISTINCT CAST([Date] AS DATE), [Event] FROM YourTable) a
GROUP BY
	[Date]

#3

I can't relate the Example Results to the data, so maybe the requirement is this?

SELECT 
	CAST([Date] AS DATE) AS [Date],
	SUM(CASE WHEN [Event] = 0 THEN 1 ELSE 0 END) AS Event_0,
	SUM(CASE WHEN [Event] = 1 THEN 1 ELSE 0 END) AS Event_1,
	SUM(CASE WHEN [Event] = 2 THEN 1 ELSE 0 END) AS Event_2,
	SUM(CASE WHEN [Event] = 3 THEN 1 ELSE 0 END) AS Event_3
FROM YourTable AS a
GROUP BY
	CAST([Date] AS DATE)