Count distinct of multiple columns-CodePudding

I've been trying to figure a query out.

Let's say a table looks like this:

cus_id   prod_category   agreement_id    type_id
111         10                123456        1
111         10                123456        1
111         10                123456        2
111         20                123456        2
123         20                987654        6
999          0                135790        99
999          0                246810        99


and so on...

I would like to get the count of prod_category for distinct values over agreement_id and type_id

so I would like to get a result like this:

cus_id     prod_id     count
111          10          2
111          20          1
123          20          1
999           0          2

CodePudding user response：

We can use the following two level aggregation query:

SELECT cus_id, prod_category, COUNT(*) AS count
FROM
(
    SELECT DISTINCT cus_id, prod_category, agreement_id, type_id
    FROM yourTable
) t
GROUP BY cus_id, prod_category;

The inner distinct query de-duplicated tuples, and the outer aggregation query counts the number of distinct tuples per customer and product category.

CodePudding user response：

You want to count distinct (agreement_id, type_id) tuples per (cus_id, prod_category) tuple.

"Per (cus_id, prod_category) tuple" translates to GROUP BY cus_id, prod_category in SQL.

And we count distinct (agreement_id, type_id) tuples with COUNT(DISTINCT agreement_id, type_id).

SELECT cus_id, prod_category, COUNT(DISTINCT agreement_id, type_id) AS distinct_count
FROM mytable
GROUP BY cus_id, prod_category
ORDER BY cus_id, prod_category;