mysql - Showing empty values in join | Sales value - category -
select cat_tbl.id_cat, cat_tbl.name, sum(expences.price) pricetotal cat_tbl join expences on cat_tbl.id_kat= expences.category group 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 have more 4 categories, tried different joins won't show categories 1-7 null values pricetotal .
i don't know how make 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 | +------+--------------+--------+
2 options left join:
select cat_tbl.id_cat, cat_tbl.name, sum(expences.price) pricetotal cat_tbl left join expences on cat_tbl.id_kat= expences.category group cat_tbl.id_cat, cat_tbl.name
or:
select cat.id_cat, cat.name, coalesce(exp.tot, 0) pricetotal cat_tbl cat left join ( select x.category, sum(x.price) tot expences x group x.category ) exp on cat.id_cat = exp.category
Comments
Post a Comment