SQL Query - Returning 0 when select count(*)

Question

First, I already read all post with same title post. But no solution solve my problem.

I want my query result's like this

kategori | hitung

 Good    |   2

 Nice    |   0

great    |   1

with this query:

SELECT kategori, COALESCE(COUNT(kategori), 0) AS hitung FROM tb_cust WHERE id_sales=9 GROUP BY kategori DESC

But the result only return 2 data:

kategori | hitung

 Good    |   2

great    |   1

Did my query is wrong?

Edit:

this is my table and the data inside of it

table name: tb_cust

id_cust  |  name_cust  |  kategori  |  id_sales

   1     |  Name 1     |  Good      |     9

   2     |  Name 2     |  Good      |     9

   3     |  Name 3     |  Great     |     9

share sample data from where you want this output

Zaynul Abadin Tuhin
– Zaynul Abadin Tuhin

2018-11-17 05:51:34 +00:00
Commented Nov 17, 2018 at 5:51 — Zaynul Abadin Tuhin
– Zaynul Abadin Tuhin, Commented Nov 17, 2018 at 5:51
Please share your table data so someone can help you

Sadikhasan
– Sadikhasan

2018-11-17 06:08:55 +00:00
Commented Nov 17, 2018 at 6:08 — Sadikhasan
– Sadikhasan, Commented Nov 17, 2018 at 6:08
Read this may help you stackoverflow.com/q/3597577/2893413

Sadikhasan
– Sadikhasan

2018-11-17 06:28:05 +00:00
Commented Nov 17, 2018 at 6:28 — Sadikhasan
– Sadikhasan, Commented Nov 17, 2018 at 6:28

George Joseph · Accepted Answer · 2018-11-17 06:25:37Z

1

The values of kategori that are available in tb_cust according to your condition(WHERE id_sales=9) is 'Good', 'Great'.

What you could have instead is

Step 1.First have a block in cte -> kategori_val to show all the possible values of kategori

Step 2. Left join with the data in tb_cust and get the count of records

--I guess "with" isnt working in mysql database

   select a.kategori,count(b.kategori) as hitung
     from (select distinct kategori /*I am assuming we have Good,Great and Nice available in tb_cust*/
             from tb_cust) a
left join tb_cust b
       on a.kategori=b.kategori
      and b.id_sales=9 
group by a.kategori 
order by 2 DESC

edited Nov 17, 2018 at 6:25

answered Nov 17, 2018 at 5:57

George Joseph

5,93213 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

George Joseph Over a year ago

I have updated the "with" clause to be an inline query. Please check

syukron hardianto Over a year ago

It nearly close, but my "nice" kategori return hitung 1 not 0

George Joseph Over a year ago

Was a silly mistake to have used count(*), it should be count(b.kategori), i have updated the answer

syukron hardianto Over a year ago

Tq this is perfect

Gordon Linoff · Accepted Answer · 2018-11-17 13:15:01Z

0

You can probably do this with conditional aggregation:

SELECT kategori, SUM(id_sales = 9) as hitung
FROM tb_cust
GROUP BY kategori DESC;

This assumes that the categories have some record in the table.

If not, you can use the lookup table for the categories:

SELECT k.kategori, SUM(c.id_sales = 9) as hitung
FROM tb_kategori k LEFT JOIN
     tb_cust c
     ON k.kategori = c.kategori
GROUP BY k.kategori DESC;

edited Nov 17, 2018 at 13:15

answered Nov 17, 2018 at 13:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL Query - Returning 0 when select count(*)

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related