Improving query that counts distinct values that have particular values in another column

Improving query that counts distinct values that have particular values in another column

Say I have a table in the format:

Say I have a table in the format:

| id | category|
|----|---------|
| 10 | A       |
| 10 | B       |  
| 10 | C       |
| 2  | C       |

I want to count the number of distinct id's that have all three values A, B, and C in the category variable. In this case, the query would return 1 since only for id = 10 is this true.

My intuition is to write the following query to get this value:

SELECT 
    COUNT(DISTINCT id), 
    SUM(CASE WHEN category = 'A' THEN 1 else 0 END) AS A,
    SUM(CASE WHEN category = 'B' THEN 1 else 0 END) AS B,
    SUM(CASE WHEN category = 'C' THEN 1 else 0 END) AS C
FROM 
    table 
GROUP BY 
    id
HAVING
    A >= 1
    AND 
    B >= 1
    AND
    C >= 1

This feels a bit overwrought though -- is there a simpler way to achieve the desired outcome?

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL

SQL Server Tutorial For Beginners - Learn MS SQL Server with Examples

SQL Server Tutorial For Beginners, Microsoft SQL Server Tutorial, SQL Server Training. Microsoft SQL Server explains all the fundamentals of MS SQL Server with examples. What is DBMS? What is SQL? What is SQL Server? SQL Server Installation

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts|Simpliv

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts