Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL query to get disctinct count of id and distinct count of sub item id

I have a database table like this:

item_id   sub_item_id
=======   ===========
1         11
1         12
1         13
2         21
2         21 -- repeat
2         22
2         23
2         24
2         25
3         31
3         31 -- repeat

Is it possible to write a query that outputs 2 counts: The count of distinct item_ids and a count of distinct sub_item_ids across all item_ids. In summary, if that query is run against the sample data above, it would produce 3, 9 as output.

I can certainly do this like below but it will produce a verbose output and I will have to then run that data through a spreadsheet to get distinct count of item_ids:

SELECT item_id, COUNT(DISTINCT(sub_item_id))
FROM my_table
GROUP BY 1
Tags:

4

Answers


  1. 0

    You can use a subquery to get the distinct combinations of item_id and sub_item_id. Then, the outer query counts the distinct item_id and the distinct sub_item_id across all the results.

    SELECT
  COUNT(DISTINCT item_id) AS distinct_item_count,
  COUNT(DISTINCT sub_item_id) AS distinct_sub_item_count
FROM (
  SELECT DISTINCT item_id, sub_item_id
  FROM my_table
) AS distinct_items;
    Login or Signup to reply.
  2. 0

    You can use two subqueries for get two counts

    SELECT 
    (SELECT COUNT(DISTINCT item_id) FROM my_table) AS distinct_item_id_count,
    (SELECT COUNT(DISTINCT sub_item_id) FROM my_table) AS distinct_sub_item_id_count
FROM 
    my_table
LIMIT 1;
    Login or Signup to reply.
  3. 0

    Just put two COUNT(DISTINCT...) calls in the SELECT list.

    SELECT
  COUNT(DISTINCT item_id) AS distinct_item_count,
  COUNT(DISTINCT sub_item_id) AS distinct_sub_item_count
FROM my_table

    DEMO

    Login or Signup to reply.
  4. 0

    See example, pointed in my comment

    select count(distinct item_id) item_qty
   ,count(distinct sub_item_id) sub_item_qty
from my_table
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search