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

MYSQL subquery using distinct

I have a table

cab name value
1 PHP 1
1 PHP 2
1 PHP 0
2 LHL 0
3 GHG 3
3 GHG 2

I am looking to get the distinct cab and name plus how many values =0 and how many are >0
the result table should look like this:

cab name value=0 value>0
1 PHP 1 2
2 LHL 1 0
3 GHG 0 2

third column represents how many values =0
forth column represents how may values >0

I’ve been trying all day but I cant seem to figure out the correct sql statement

Tags:

3

Answers


  1. 0

    I know that the title say "subquery using distinct", but the easier way to solve problem like this is using CTEs and the base value tecnic.

    I wrote this SQL that solve the problem.

    with CTE1 as (
   select cab, name, count(*) as v1
   from tablename
   where value = 0
   group by cab, name
), CTE2 as (
   select cab, name, count(*) as v2
   from tablename
   where value > 0
   group by cab, name 
), base_value AS (
   select cab, name
   from tablename
   group by cab, name
)
select cab, name, ifnull(CTE1.v1,0) as "Value = 0", ifnull(CTE2.v2,0)  as "Value > 0"
from base_value
   natural left join CTE1
   natural left join CTE2;

    This simple code use 3 CTEs. The first (CTE1) count how many times there is value = 0. The second (CTE2) count how many time there is value > 0. The last CTE (base_value) is just a List with all the possibile combination of Cap and Name. After declering this CTE, you can simply union the result in the last statement.

    I hope this will help you. Sorry for not using the subquey (you can try to convert this code if you realy need that) and sorry for my bad english too.

    Login or Signup to reply.
  2. 0

    I got two options for you.It works fine in your test case ^^

    SELECT 
    t1.cab,
    t1.name,
    COALESCE(t2.value_0, 0) as value_0,
    COALESCE(t3.value_greater_than_0, 0) as value_greater_than_0
FROM 
    (SELECT DISTINCT cab, name FROM [your_table_name]) AS t1
LEFT JOIN 
    (SELECT cab, name, COUNT(*) as value_0 FROM [your_table_name] WHERE value = 0 GROUP BY cab, name) AS t2
ON 
    t1.cab = t2.cab AND t1.name = t2.name
LEFT JOIN 
    (SELECT cab, name, COUNT(*) as value_greater_than_0 FROM [your_table_name] WHERE value > 0 GROUP BY cab, name) AS t3
ON 
    t1.cab = t3.cab AND t1.name = t3.name;

    SELECT 
    cab,
    name,
    SUM(CASE WHEN value = 0 THEN 1 ELSE 0 END) as value_0,
    SUM(CASE WHEN value > 0 THEN 1 ELSE 0 END) as value_greater_than_0
FROM 
    [your_table_name]
GROUP BY 
    cab, name
    order by cab
    Login or Signup to reply.
  3. 0

    No need for a subquery or DISTINCT here, that’s a use case of conditional aggregation.

    We will simply use GROUP BY and sum the occurences of values = 0 and values > 0.

    If the column value is not nullable, this will do:

    SELECT 
  cab,
  name,
  SUM(value = 0) AS "value=0",
  SUM(value > 0) AS "value>0"
FROM 
  yourtable
GROUP BY 
  cab, name
ORDER BY 
  cab;

    If the column is nullable, the above query can return NULL. If this is not intended, we can use SUM(IF...) to enforce a result of 0 in such cases:

    SELECT 
  cab,
  name,
  SUM(IF(value = 0,1,0)) AS "value=0",
  SUM(IF(value > 0,1,0)) AS "value>0"
FROM 
  yourtable
GROUP BY 
  cab, name
ORDER BY 
  cab

    Try out here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search