I would like to create a new query for know how many product are in the store.
table: tb_store
+--------+------------+------------------+-----------+
| item_id| nome | date_out | date_in |
+--------+------------+------------------+-----------+
| 1 | Produ1 | null | 2015-01-06|
| 2 | Produ1 | null | 2015-01-06|
| 3 | Produ3 | null | 2015-01-06|
| 3 | Produ3 | null | 2015-01-06|
| 3 | Produ5 | null | 2015-01-06|
+--------+------------+------------------+-----------+
table: tb_product
+--------+------------+
| item_id| nome |
+--------+------------+
| 1 | Produ1 |
| 2 | Produ2 |
| 3 | Produ3 |
| 3 | Produ4 |
| 3 | Produ5 |
+--------+------------+
i have write this query:
select nome, count(nome) as pezzi from tb_store where data_out is null or data_out="" group by nome order by pezzi desc
the result are:
+--------+------------+
| nome | pezzi |
+--------+------------+
| Produ1 | 2 |
| Produ3 | 2 |
| Produ5 | 1 |
+--------+------------+
i would like to obtain this result:
+--------+------------+
| nome | pezzi |
+--------+------------+
| Produ1 | 2 |
| Produ3 | 2 |
| Produ5 | 1 |
| Produ2 | 0 |
| Produ4 | 0 |
+--------+------------+
is possible? how can rewrite the query?
Aucun commentaire:
Enregistrer un commentaire