Merge two Group By Rows

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

CREATE TABLE #MyLogs
    ([VehicleMonitoringId] int, [SiteId] int, [SiteName] varchar(10), [DateTime] datetime, [EventCode] int)
;
    
INSERT INTO #MyLogs
    ([VehicleMonitoringId], [SiteId], [SiteName], [DateTime], [EventCode])
VALUES
    (1185696, 1, 'Abbottabad', '2016-02-29 19:08:51', 43),
    (1185699, 1, 'Abbottabad', '2016-02-29 19:08:54', 43),
    (1185724, 1, 'Abbottabad', '2016-02-29 19:10:44', 43),
    (1185730, 1, 'Abbottabad', '2016-02-29 19:11:14', 43),
    (1185733, 1, 'Abbottabad', '2016-02-29 19:11:22', 43),
    (1185795, 1, 'Abbottabad', '2016-02-29 19:19:24', 43),
    (1185799, 1, 'Abbottabad', '2016-02-29 19:19:51', 43),
    (1185802, 1, 'Abbottabad', '2016-02-29 19:20:05', 43),
    (1185806, 1, 'Abbottabad', '2016-02-29 19:20:10', 43),
    (1185812, 1, 'Abbottabad', '2016-02-29 19:20:40', 43),
    (1185814, 1, 'Abbottabad', '2016-02-29 19:20:48', 43),
    (1185820, 1, 'Abbottabad', '2016-02-29 19:21:21', 43),
    (1185855, 1, 'Abbottabad', '2016-02-29 19:27:22', 43),
    (1185861, 1, 'Abbottabad', '2016-02-29 19:28:18', 43),
    (1185865, 1, 'Abbottabad', '2016-02-29 19:29:19', 43),
    (1185868, 1, 'Abbottabad', '2016-02-29 19:29:23', 43),
    (1185888, 1, 'Abbottabad', '2016-02-29 19:31:59', 43),
    (1185892, 1, 'Abbottabad', '2016-02-29 19:32:03', 43),
    (1185896, 1, 'Abbottabad', '2016-02-29 19:32:29', 43),
    (1185898, 1, 'Abbottabad', '2016-02-29 19:32:32', 43),
    (1185900, 1, 'Abbottabad', '2016-02-29 19:32:35', 43),
    (1185902, 1, 'Abbottabad', '2016-02-29 19:32:37', 43),
    (1185904, 1, 'Abbottabad', '2016-02-29 19:32:40', 43),
    (1185906, 1, 'Abbottabad', '2016-02-29 19:32:48', 43),
    (1185908, 1, 'Abbottabad', '2016-02-29 19:32:52', 43),
    (1185910, 1, 'Abbottabad', '2016-02-29 19:33:07', 43),
    (1185914, 1, 'Abbottabad', '2016-02-29 19:33:11', 43),
    (1185916, 1, 'Abbottabad', '2016-02-29 19:33:53', 43),
    (1185918, 1, 'Abbottabad', '2016-02-29 19:34:23', 43),
    (1185922, 1, 'Abbottabad', '2016-02-29 19:34:31', 43),
    (1185697, 1, 'Abbottabad', '2016-02-29 19:08:52', 44),
    (1185700, 1, 'Abbottabad', '2016-02-29 19:08:55', 44),
    (1185726, 1, 'Abbottabad', '2016-02-29 19:10:47', 44),
    (1185732, 1, 'Abbottabad', '2016-02-29 19:11:16', 44),
    (1185734, 1, 'Abbottabad', '2016-02-29 19:11:23', 44),
    (1185798, 1, 'Abbottabad', '2016-02-29 19:19:49', 44),
    (1185801, 1, 'Abbottabad', '2016-02-29 19:20:02', 44),
    (1185805, 1, 'Abbottabad', '2016-02-29 19:20:08', 44),
    (1185811, 1, 'Abbottabad', '2016-02-29 19:20:38', 44),
    (1185924, 1, 'Abbottabad', '2016-02-29 19:34:55', 43),
    (1185926, 1, 'Abbottabad', '2016-02-29 19:34:57', 43),
    (1185932, 1, 'Abbottabad', '2016-02-29 19:35:15', 43),
    (1185934, 1, 'Abbottabad', '2016-02-29 19:35:18', 43),
    (1185937, 1, 'Abbottabad', '2016-02-29 19:35:21', 43),
    (1185939, 1, 'Abbottabad', '2016-02-29 19:35:48', 43),
    (1185953, 1, 'Abbottabad', '2016-02-29 19:40:37', 43),
    (1185991, 1, 'Abbottabad', '2016-02-29 19:57:13', 43),
    (1185995, 1, 'Abbottabad', '2016-02-29 19:59:01', 43),
    (1186001, 1, 'Abbottabad', '2016-02-29 20:00:46', 43),
    (1186003, 1, 'Abbottabad', '2016-02-29 20:00:50', 43),
    (1186005, 1, 'Abbottabad', '2016-02-29 20:01:01', 43),
    (1186009, 1, 'Abbottabad', '2016-02-29 20:01:04', 43),
    (1186011, 1, 'Abbottabad', '2016-02-29 20:01:08', 43),
    (1186013, 1, 'Abbottabad', '2016-02-29 20:01:15', 43),
    (1186015, 1, 'Abbottabad', '2016-02-29 20:01:34', 43),
    (1186017, 1, 'Abbottabad', '2016-02-29 20:02:03', 43),
    (1186019, 1, 'Abbottabad', '2016-02-29 20:02:12', 43),
    (1186024, 1, 'Abbottabad', '2016-02-29 20:04:36', 43),
    (1186026, 1, 'Abbottabad', '2016-02-29 20:04:44', 43),
    (1186028, 1, 'Abbottabad', '2016-02-29 20:05:26', 43),
    (1186036, 1, 'Abbottabad', '2016-02-29 20:07:05', 43),
    (1186063, 1, 'Abbottabad', '2016-02-29 20:18:54', 43),
    (1185813, 1, 'Abbottabad', '2016-02-29 19:20:47', 44),
    (1185819, 1, 'Abbottabad', '2016-02-29 19:21:20', 44),
    (1185822, 1, 'Abbottabad', '2016-02-29 19:21:50', 44),
    (1185860, 1, 'Abbottabad', '2016-02-29 19:28:17', 44),
    (1185864, 1, 'Abbottabad', '2016-02-29 19:29:17', 44),
    (1185866, 1, 'Abbottabad', '2016-02-29 19:29:22', 44),
    (1185887, 1, 'Abbottabad', '2016-02-29 19:31:57', 44),
    (1185891, 1, 'Abbottabad', '2016-02-29 19:32:02', 44),
    (1185895, 1, 'Abbottabad', '2016-02-29 19:32:26', 44),
    (1185897, 1, 'Abbottabad', '2016-02-29 19:32:31', 44),
    (1185899, 1, 'Abbottabad', '2016-02-29 19:32:34', 44),
    (1185901, 1, 'Abbottabad', '2016-02-29 19:32:36', 44),
    (1185903, 1, 'Abbottabad', '2016-02-29 19:32:39', 44),
    (1185905, 1, 'Abbottabad', '2016-02-29 19:32:43', 44),
    (1185907, 1, 'Abbottabad', '2016-02-29 19:32:49', 44),
    (1185909, 1, 'Abbottabad', '2016-02-29 19:32:54', 44),
    (1185911, 1, 'Abbottabad', '2016-02-29 19:33:08', 44),
    (1185915, 1, 'Abbottabad', '2016-02-29 19:33:52', 44),
    (1185917, 1, 'Abbottabad', '2016-02-29 19:34:14', 44),
    (1185919, 1, 'Abbottabad', '2016-02-29 19:34:29', 44),
    (1185923, 1, 'Abbottabad', '2016-02-29 19:34:32', 44),
    (1185925, 1, 'Abbottabad', '2016-02-29 19:34:56', 44),
    (1185927, 1, 'Abbottabad', '2016-02-29 19:34:58', 44),
    (1185933, 1, 'Abbottabad', '2016-02-29 19:35:17', 44),
    (1185936, 1, 'Abbottabad', '2016-02-29 19:35:19', 44),
    (1185938, 1, 'Abbottabad', '2016-02-29 19:35:22', 44),
    (1185940, 1, 'Abbottabad', '2016-02-29 19:36:06', 44),
    (1185990, 1, 'Abbottabad', '2016-02-29 19:57:06', 44),
    (1185992, 1, 'Abbottabad', '2016-02-29 19:57:14', 44),
    (1186000, 1, 'Abbottabad', '2016-02-29 20:00:44', 44),
    (1186002, 1, 'Abbottabad', '2016-02-29 20:00:48', 44),
    (1186004, 1, 'Abbottabad', '2016-02-29 20:00:58', 44),
    (1186006, 1, 'Abbottabad', '2016-02-29 20:01:03', 44),
    (1186010, 1, 'Abbottabad', '2016-02-29 20:01:07', 44),
    (1186012, 1, 'Abbottabad', '2016-02-29 20:01:13', 44),
    (1186014, 1, 'Abbottabad', '2016-02-29 20:01:22', 44),
    (1186016, 1, 'Abbottabad', '2016-02-29 20:01:38', 44),
    (1186018, 1, 'Abbottabad', '2016-02-29 20:02:04', 44),
    (1186023, 1, 'Abbottabad', '2016-02-29 20:04:33', 44),
    (1186025, 1, 'Abbottabad', '2016-02-29 20:04:38', 44),
    (1186027, 1, 'Abbottabad', '2016-02-29 20:04:45', 44),
    (1186029, 1, 'Abbottabad', '2016-02-29 20:05:27', 44),
    (1186041, 1, 'Abbottabad', '2016-02-29 20:10:02', 44)
;

--Select * FROM #MyLogs



	SELECT 
		SiteId, 
		SiteName, 
		SUM(CASE EventCode WHEN 43 THEN 1 ELSE 0 END) AS [OpenedCount],
		SUM(CASE EventCode WHEN 44 THEN 1 ELSE 0 END) AS [ClosedCount]
	FROM #MyLogs
			GROUP BY SiteId, 
					SiteName;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results