SQL多列Distinct Count：如何统计各型号对应的唯一用户数量

Hey there! No worries at all—starting out with SQL can feel a little overwhelming, but this is a super common task once you get the hang of grouping and counting distinct values.

First, let's assume your table has columns like model (for the device/model name) and user_id (or some unique identifier for each user). Let's say your table is named device_usage—you can swap that out for your actual table name.

Here's the SQL query you need:

SELECT 
    model,
    COUNT(DISTINCT user_id) AS unique_users_count
FROM 
    device_usage
GROUP BY 
    model;

Let me break down what each part does:

• SELECT model: We want to display each unique model in our final results.
• COUNT(DISTINCT user_id): This is the key part—it counts only unique users per model. So if the same user uses a model multiple times, they're only counted once, which exactly matches your requirement: Model X with users A & D = 2, Model Y with B & C = 2, Model Z with E = 1. We use AS unique_users_count to give the result column a clear, readable name.
• GROUP BY model: This groups all our rows together by the model name, ensuring the count calculation runs separately for each model instead of across the entire table.

Example to verify #

If your table had data like this:

Running the query would return:

Just remember to replace device_usage, model, and user_id with your actual table and column names if they're different!

内容的提问来源于stack exchange，提问作者user517696