COUNT () OVER (PARTITION BY) and DISTINCT

Database

Re: COUNT () OVER (PARTITION BY) and DISTINCT

I see alot of these posts are similar to what I need yet a bit different.  I'm trying to get a distinct PE_ID_ID and count the number of times date_created with the same hour, in this case either 1PM, 2PM, 3PM or 4PM:

Looking to convert this:

PE_ID_id                   date_created

0002070001020000 2016-07-22 13:29:03.6149950

0002070001020000 2016-07-22 13:33:17.9413390

0002070001020000 2016-07-22 14:14:08.7322900

0002070001020000 2016-07-22 14:40:41.0482080

0002070001020000 2016-07-22 14:52:33.3103760

0002070001020000 2016-07-22 15:04:47.1281310

0002070001020000 2016-07-22 15:17:41.0911360

0002070001020000 2016-07-22 15:35:40.8175370

0002070001020000 2016-07-22 15:40:16.7062010

0002070001020000 2016-07-22 15:50:16.5956700

0002070001020000 2016-07-22 16:07:57.7761330

0002070001040000 2016-07-22 16:08:36.3324330

0002070001040000 2016-07-22 15:39:49.0625400

0002070001040000 2016-07-22 15:51:02.1318350

0002070001040000 2016-07-22 15:16:19.0790620

0002070001040000 2016-07-22 15:29:05.9620040

0002070001040000 2016-07-22 14:51:07.1357490

0002070001040000 2016-07-22 15:01:34.5495330

0002070001040000 2016-07-22 14:41:27.5670580

0002070001040000 2016-07-22 14:33:56.5452800

0002070001040000 2016-07-22 14:20:47.4194970

0002070001040000 2016-07-22 14:00:40.4712860

0002070001040000 2016-07-22 13:41:27.1271900

0002070001040000 2016-07-22 13:20:26.3539930

0002070001040000 2016-07-22 13:24:10.4667830

0002070001060000 2016-07-22 13:24:29.1956960

0002070001060000 2016-07-22 13:28:18.7786330

0002070001060000 2016-07-22 13:53:45.7635220

0002070001060000 2016-07-22 14:13:51.6734760

0002070001060000 2016-07-22 14:23:03.6995150

0002070001060000 2016-07-22 14:36:15.3188540

0002070001060000 2016-07-22 14:48:14.1417780

0002070001060000 2016-07-22 15:00:05.5083840

0002070001060000 2016-07-22 15:23:46.4888220

0002070001060000 2016-07-22 15:10:56.0630850

0002070001060000 2016-07-22 15:35:58.8465860

0002070001060000 2016-07-22 16:07:40.8866710

0002070001060000 2016-07-22 15:45:38.4578730

to this

PE_ID_ID                  1-2   2-3   3-4   4-5

0002070001020000 2       3      5       1

0002070001040000 3       5      5       1

0002070001060000 3       4      5       1

Re: COUNT () OVER (PARTITION BY) and DISTINCT

 sel  distinct name,hair_colour,

count(*) over (partition by name,hair_colour order by name )
 from MyTable

Highlighted
Junior Contributor

Re: COUNT () OVER (PARTITION BY) and DISTINCT

There's no need for a Window Count or DISTINCT, it's a simple conditional aggregation:

SELECT PE_ID_id
   ,Count(CASE WHEN Extract(HOUR From date_created) = 13 THEN 1 end) as "1-2"
   ,Count(CASE WHEN Extract(HOUR From date_created) = 14 THEN 1 end) as "2-3"
   ,Count(CASE WHEN Extract(HOUR From date_created) = 15 THEN 1 end) as "3-4"
   ,Count(CASE WHEN Extract(HOUR From date_created) = 16 THEN 1 end) as"4-5"
FROM mytable
GROUP BY 1

 

Re: COUNT () OVER (PARTITION BY) and DISTINCT

sorry the above query which i wrote is for another question ..!!

Re: COUNT () OVER (PARTITION BY) and DISTINCT

 sel  distinct name,hair_colour,

count(*) over (partition by name,hair_colour order by name )
 from MyTable