CODEWITHSUNDEEP
mysqlsql
Kavvson Empcraft
Kavvson Empcraft

Reputation: 443

Showing empty values in join | Sales value - category

SELECT
   cat_tbl.id_cat, 
   cat_tbl.name, 
   Sum(expences.price) AS PriceTotal
FROM cat_tbl
  JOIN  expences ON cat_tbl.id_kat= expences.category

GROUP BY
    cat_tbl.id_cat, 
    cat_tbl.name

Result 

+------+--------------+--------+
| name | PriceTotal   | id_cat |
+------+--------------+--------+
| Cat1 |      1031.40 |      1 |
| Cat2 |       200.88 |      2 |
| Cat4 |        46.44 |      4 |
| Cat5 |       223.76 |      5 |
+------+--------------+--------+

The thing is I have more than 4 categories, I tried with different joins but they won't show the categories 1-7 with null values for PriceTotal .

I really don't know how to make it as such

+------+--------------+--------+
| name | PriceTotal   | id_cat |
+------+--------------+--------+
| Cat1 |      1031.40 |      1 |
| Cat2 |       200.88 |      2 |
| Cat3 |         0    |      3 |
| Cat4 |        46.44 |      4 |
| Cat5 |       223.76 |      5 |
| Cat6 |       0      |      6 |
| Cat7 |       0      |      7 |
+------+--------------+--------+

Upvotes: 1

Views: 41

Answers (2)

user8207463
user8207463

Reputation: 

   SELECT ct.id_cat, ct.name, Sum(ex.price) AS PriceTotal
   FROM cat_tbl ct
   JOIN  expences ex ON ct.id_cat= ex.category
   LEFT JOIN expences ex2 ON (ct.id_cat = ex2.category AND ex.category < ex2.category)
   .....
   WHERE ex2.category IS NULL

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

2 options for your left join:

SELECT
   cat_tbl.id_cat, 
   cat_tbl.name, 
   Sum(expences.price) AS PriceTotal
FROM cat_tbl
  LEFT JOIN  expences 
     ON cat_tbl.id_kat= expences.category
GROUP BY
    cat_tbl.id_cat, 
    cat_tbl.name

or:

select cat.id_cat, cat.name,
       coalesce(exp.tot, 0) as PriceTotal
from cat_tbl cat
left join
(
select x.category, sum(x.price) as tot
from expences x
group by x.category
) exp
on cat.id_cat = exp.category

Upvotes: 2

Related Questions