Can I get two count results in a query?

Question

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?

Oh, I'm sorry. Your answer also worked well as I intend, but I thought the second answer had better legibility. — seunghyeon, Sep 27 '22 at 08:18

score 1 · Answer 1 · answered Sep 12 '22 at 13:08

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;

Chris Maurer · Accepted Answer · 2022-09-12T13:31:00.793

0

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

edited Sep 12 '22 at 13:31

answered Sep 12 '22 at 13:11

Chris Maurer

2,339
1
9
8

When I add `ELSE 0` in this query, the result of expired and total is same. I don't know the reason, but I think `ELSE 0` should be removed. – seunghyeon Sep 12 '22 at 13:22
Please let me know if anyone knows why. – seunghyeon Sep 12 '22 at 13:23
I can't see what you've done, but did you still COUNT the Case function? You need to SUM it. – Chris Maurer Sep 12 '22 at 13:26
I think `1` in `THEN 1` means `true`, not one. Likewise, it seems `ELSE 0` also means `true` like `1` in this query. – seunghyeon Sep 12 '22 at 13:27
Oh, I misunderstood. I mistook sum for count. Your answer works well as what I want. – seunghyeon Sep 12 '22 at 14:04

Can I get two count results in a query?

2 Answers2