CODEWITHSUNDEEP
sqlpostgresql
ZJAY
ZJAY

Reputation: 2807

SQL Group By for quarterly dates

transaction_date is in a date format.
What I'm actually trying to output is the COUNT DISTINCT of Unique_ID by quarter (i.e., how many times did a Unique_Id appear in a given quarter).

SELECT  transaction_date ,
    UNIQUE_ID,
FROM    panel
     WHERE (some criteria = 'x') 
GROUP BY UNIQUE_ID

Upvotes: 1

Views: 3022

Answers (3)

Hambone
Hambone

Reputation: 16387

I'd use date_trunc:

select
  date_trunc ('quarter', transaction_date), count (distinct unique_id)
from panel
where criteria = 'x'
group by 1

This presupposes that when you say "by quarter" that 1Q2015 is different than 1Q2014.

Upvotes: 2

Raffaello.D.Huke
Raffaello.D.Huke

Reputation: 552

try this :

SELECT  datepart(quarter,transaction_date),
count(distinct UNIQUE_ID) as cnt
FROM    panel
WHERE (some criteria = 'x') 
GROUP BY datepart(quarter,p.transaction_date)

but the count(distinct) will do a sort so it will take you a lot of time. so you can distinct it first in the table then do the count 

SELECT  datepart(quarter,p.transaction_date),
count(p.UNIQUE_ID) as cnt
FROM   (select distinct transaction_date as transaction_date, UNIQUE_ID
        from panel) as p
WHERE (some criteria = 'x') 
GROUP BY datepart(quarter,p.transaction_date)

Upvotes: 2

gogolaygo
gogolaygo

Reputation: 325

SELECT DATEPART(QUARTER, transaction_date) ,
COUNT(DISTINCT UNIQUE_ID),
FROM panel
GROUP BY transaction_date

Upvotes: 0

Related Questions