CODEWITHSUNDEEP
sqlsql-server
Paras
Paras

Reputation: 3481

How to select different count data in one row in sql server

I am trying to select certain data from a table on sql server which has a following structure :

TABLE A
    |TYPE_ID| |CT| |DUE_DATE|

Now the output that I need is :

|TYPE_ID| |COUNT_TODAY| |COUNT_ONE_WEEK| |COUNT_ONE_MONTH|

How can I select all the counts in one row?

I can do that in multiple rows because there I can have different rows on each having it's own where clause comparing dates.

|TYPE_ID| |COUNT| |IS_TODAY| |IS_ONE_MONTH|

I can get this by following query :

SELECT COUNT(CT),1,0 FROM A WHERE DUE_DATE = GETDATE() 
SELECT COUNT(CT),0,1 FROM A WHERE DUE_DATE >= DATEADD(DAY,-7,GETDATE())

However I need the Output as describe above/

Upvotes: 1

Views: 101

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use conditional aggregation:

select typeid,
       sum(case when cast(due_date as date) = cast(getdate() as date) then 1 else 0 end) as today,
       sum(case when cast(due_date as date) >= cast(getdate() - 7 as date) then 1 else 0 end) as week,
       sum(case when cast(due_date as date) >= dateadd(month, -1, cast(getdate() as date)) then 1 else 0 end) as month
from t
group by typeid;

Upvotes: 2

Related Questions