CODEWITHSUNDEEP
mysqlsql
priyo
priyo

Reputation: 85

Join two tables, count the first table based on multiple conditions in the second table

How to join two tables, count the first table based on multiple conditions in the second table

This is my tables :

  1. category table

| id |category|
+----+--------+
| 1  | food   |
| 2  | drinks |
+----+--------+
  1. product tabel

| id |category| name  | entry      | verified   |
+----+--------+--------------------+------------+
| 1  |   1    | rice  | 2020-05-13 | 2020-05-25 |
| 2  |   1    | noodle| 2020-05-18 | 0000-00-00 |
| 3  |   2    | Milk  | 2020-05-15 | 0000-00-00 |
| 4  |   2    | Syrup | 2020-05-20 | 0000-00-00 |
+----+--------+-------+------------+------------+

I want to get list of categories along with count of number of products verified as follow.

+----+--------+-------+----------+
| No |category| entry | verified |
+----+--------+-------+----------+
|  1 | food   |   1   |     1    |
|  2 | drinks |   2   |     0    |
+----+--------+-------+----------+

i process it with php script and mysql database. I have trouble to make query to get list of those categories can anyone help me make the query? Thank you for your help

Upvotes: 0

Views: 748

Answers (2)

Nick
Nick

Reputation: 147166

You can use conditional aggregation to achieve the results you want:

SELECT c.id AS No,
       c.category,
       COUNT(CASE WHEN p.entry != '0000-00-00' AND p.verified = '0000-00-00' THEN 1 END) AS entry,
       COUNT(CASE WHEN p.verified != '0000-00-00' THEN 1 END) AS verified
FROM category c
LEFT JOIN product p ON p.category = c.id
GROUP BY c.id, c.category

Output:

No  category    entry   verified
1   food        1       1
2   drinks      2       0

Demo on dbfiddle

In MySQL you can simplify the COUNT to a SUM of the condition, since MySQL treats booleans as 1 or 0 in a numeric context:

SELECT c.id AS No,
       c.category,
       SUM(p.entry != '0000-00-00' AND p.verified = '0000-00-00') AS entry,
       SUM(p.verified != '0000-00-00') AS verified
FROM category c
LEFT JOIN product p ON p.category = c.id
GROUP BY c.id, c.category

The output is the same for this query. Demo on dbfiddle

Upvotes: 2

sachin kumara liyanage
sachin kumara liyanage

Reputation: 504

SELECT
    cat.id as `No`,
    cat.category,
    SUM(IF(pro.verified = '0000-00-00' and pro.entry != '0000-00-00', 1, 0)) as entry,
    SUM(IF(pro.verified != '0000-00-00', 1, 0)) as verified
FROM
    category AS cat
    INNER JOIN
    product AS pro
    ON 
        cat.id = pro.category
GROUP BY
    pro.category;

Upvotes: 0

Related Questions