How to combine results from a query into columns of a new table

I have a list of dates, and I am trying to find the number of expired / nearly expired and OK dates and build a new table showing these.

The easiest method for me is to simply, query my database three times and build the expected results in the my app. I am just wondering if SQL would be more cleaner.

I have only managed to produce three distinct correct results, but I need to the final result to be in it’s own table.

I have a SQLFiddle showing what I have done so far http://www.sqlfiddle.com/#!18/c7b1a/1

My table is

CREATE TABLE certificates (
    ValidTo DATETIME
);

Data is

INSERT INTO certificates (ValidTo) VALUES ('2019-05-12') -- expired
INSERT INTO certificates (ValidTo) VALUES ('2019-06-20') -- due
INSERT INTO certificates (ValidTo) VALUES ('2019-06-25') -- due
INSERT INTO certificates (ValidTo) VALUES ('2020-01-25') -- ok
INSERT INTO certificates (ValidTo) VALUES ('2020-03-25') -- ok
INSERT INTO certificates (ValidTo) VALUES ('2020-03-05') -- ok

The SQL I have come up with so far is

select count(*) [Expired] from certificates where ValidTo < '2019-06-12'
GO
select count(*) [Due] from certificates where ValidTo >= '2019-06-12' and ValidTo <= '2019-07-12'
GO
select count(*) [OK] from certificates where ValidTo > '2019-07-12'

However, this will obviously show three seperate results.

Ideally, I want each result in a column of a new table

My expected result would be

Expired   Due   OK
1         2     3

Where Expired = 1 because first SQL query returns 1

Where Due = 2 because second SQL query returns 2

Where OK = 3 because third SQL query returns 3

Answer

Use conditional aggregation with case when expression

DEMO

select 
   count(case when ValidTo < '2019-06-12' then 1 end) as [Expired],
   count(case when ValidTo >= '2019-06-12' and ValidTo <= '2019-07-12' then 1 end) as [Due],
   count(case when ValidTo > '2019-07-12' then 1 end) as [OK]
from certificates

OUTPUT:

Expired Due OK
1       2   3

Advertisement

Answer