CODEWITHSUNDEEP
phpmysqlcountpivotwhere-clause
Weber
Weber

Reputation: 75

MySQL multiple count by one query

I try to count how many were sold by the month and get one Array.

$q = SELECT COUNT(id) AS January FROM tableName WHERE status='sold' AND month = '1'
UNION 
SELECT COUNT(id) AS February FROM tableName WHERE status='sold' AND month = '2'

$row = mysqli_fetch_array(mysqli_query($connection, $q));

print_r($row);

UNION don't work, when I'm trying print_r(array), I get result

[Jenuary] => 200

How do I get one array in both months?

I want result:

[January] => 200,
[February] => 221

Upvotes: 0

Views: 98

Answers (2)

Jigs
Jigs

Reputation: 96

You can try this query to get exact result you want.

select MONTHNAME(STR_TO_DATE(month, '%m')) as month, count(*) cnt
from tablename
where status = 'sold'
group by month

Only for specific month

 select MONTHNAME(STR_TO_DATE(month, '%m')) as month, count(*) cnt
    from tablename
    where status = 'sold' and month in(1, 2)
    group by month

Upvotes: 2

GMB
GMB

Reputation: 222492

I think you want conditional aggregation:

select sum(month = 1) as january, sum(month = 2) as february
from tablename
where status = 'sold' and month in (1, 2)

This generates just one row, with two columns called january and february, each containing the number of rows for that month.

Or maybe you want one row per month. In that case you can use simple aggregation:

select month, count(*) cnt
from tablename
where status = 'sold' and month in (1, 2)
group by month

Upvotes: 1

Related Questions