How to get count for different results on same table

Question

I have a set of queries that represent the data from different sysdates (from the last 5, 7 and 30 days).

My doubt is how to express in a query this results in this matter:

STATE | 5 DAYS | 7 DAYS | 30 DAYS
---------------------------------
INIT  |   1    |   1    |    2
---------------------------------
SECN  |   2    |   2    |    2

NOTE: This is from a single table with different sysdates in consideration

NOTE2: An query example is this

select 
CASE WHEN STATUS = 'INI' then 'Initial' 
WHEN STATUS = 'SECN' the 'Second'
END 'Status', count(*)
from db.FilesTable
where 1=1
and DAT_Files >= DATEADD(day,-5,GETDATE())

I don't understand what you're trying to ask here. Sample data and expected results will really help here. What have you tried to resolve the problem yourself (post your SQL attempts). I suspect this is a conditional aggregate problem (Something like `COUNT(CASE WHEN {Column} <= 5 THEN 1 END) AS Count5`). — Thom A, Jan 08 '19 at 14:58

score 1 · Accepted Answer · answered Jan 08 '19 at 15:18

Use conditional aggregation to count records only when a particular condition occurs. The CASE will be computed before the aggregation occurs, so you can put any expression on any column.

select
    State = T.Status,
    [5 Days] = COUNT(CASE WHEN T.DAT_Files >= DATEADD(day, -5, GETDATE()) THEN 1 END),
    [7 Days] = COUNT(CASE WHEN T.DAT_Files >= DATEADD(day, -7, GETDATE()) THEN 1 END),
    [30 Days] = COUNT(1)
from 
    db.FilesTable AS T
where
    T.Status IN ('INI', 'SECN') AND
    DAT_Files >= DATEADD(day, -30, GETDATE()) -- Biggest period filter here
GROUP BY
    T.Status

score 0 · Answer 2 · answered Jan 08 '19 at 15:01

I think you could write a query of this type:

SELECT State,
       SUM (CASE WHEN ColA < 6 THEN 1 ELSE 0 END) AS '5 Days',
       SUM (CASE WHEN ColA IN (6, 7) THEN 1 ELSE 0 END) AS '7 Days',
       SUM (CASE WHEN ColA > 7 AND ColA < 31 THEN 1 ELSE 0 END) AS '30 Days'
FROM TableA

Obviously you might want to adjust something, but this gives some direction.

How to get count for different results on same table

2 Answers2