Can I get two count results in a query?-CodePudding

I am using MariaDB. I want to have two count results in a query.

The first query is:

SELECT 
    license_key, 
    COUNT( * ) AS expired
FROM license_product
WHERE expire_at > NOW()
GROUP BY license_key;

Next is:

SELECT 
    license_key, 
    COUNT( * ) AS total
FROM license_product
GROUP BY license_key;

What I want to get is:

 --------------------- --------- --------- 
| license_key         | expired |   total |
 --------------------- --------- --------- 
| 0DSX-1DXW-ONYK-3QJS |       5 |      10 |
| 1IBR-GSZ4-AHPK-898F |       4 |       8 |
| 4BDD-YQBD-5QGG-XS70 |       2 |       2 |
| 5CJF-O3LY-WSA8-ZKWK |       3 |       5 |
 --------------------- --------- ---------

How can I combine them?

CodePudding user response：

Use conditional aggregation:

SELECT license_key, 
       SUM(expire_at > NOW()) AS expired,
       COUNT(*) AS total
FROM license_product
GROUP BY license_key;

or:

SELECT license_key, 
       COUNT(CASE WHEN expire_at > NOW() THEN 1 END) AS expired,
       COUNT(*) AS total
FROM license_product
GROUP BY license_key;

CodePudding user response：

The usual way is to make a Case..When statement that is one or zero for the desired condition and then SUM it, not COUNT it.

Select license_key,
    sum(Case When expire_at > NOW() Then 1 Else 0 End) as expired,
    COUNT( * ) AS total
FROM license_product
GROUP BY license_key