CODEWITHSUNDEEP
mysql
hamiid reza Gholami
hamiid reza Gholami

Reputation: 115

how to get count of products that are not stored yet in mysql

hello i have an order_table with product_code that this product_code has no relation with another table and is populating directly from my app i have 8 product_code in my app and i want to get count of all product per order like this from order_table

prodouct_code|count
code1        |     5
code2        |     0    
code3        |     2    
code4        |     0    
....    
code8        |     4

but i only can get the count of existing products.. is there any way to get them all ?

enter code here

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

This is probably more easily done in your application. But, you can do it in MySQL. The normal method is to have a table with one row per product:

select p.productcode, count(o.productcode)
from products p left join
     orders o
     on p.productcode = o.productcode
group by p.productcode;

If you don't have such a table, you should probably create one. But, you can do what you want by using a derived table in the query:

select p.productcode, count(o.productcode)
from (select 1 as productcode union all
      select 2 union all
      . . . 
      select 8
     ) p left join
     orders o
     on p.productcode = o.productcode
group by p.productcode;

Upvotes: 3

Related Questions