CODEWITHSUNDEEP
phpmysql
rusly
rusly

Reputation: 1522

MySQL count of value in colum

I have one colum package and contain package id 1, 2, 3, 4. Right now my query will select all table stores_profile and stores_fee and filter by date and group by month

Below is my database :

Table : stores_profile

enter image description here

Table : stores_fee

enter image description here

and here is my current query :

<?php
$filter_date = '2013-04-01';

$result = mysql_query("SELECT sp.*, sf.*, sp.id as store_profile_id,DATE_FORMAT(sf.date_start, '%d-%m-%Y') as month
 FROM `stores_profile` sp
 left join `stores_fee` sf ON sp.id=sf.store_id 
 where sf.date_start >= '".$filter_date."' GROUP BY MONTH(sf.date_start) ");

while($row = mysql_fetch_array($result)) {
    $month = date('M', strtotime($row['month']));
    $package = $row['package']; 
?>
      <tr>
        <td><?php echo $month ?>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
<?php } echo '</table>'; ?>

and result for this code is :

enter image description here

So, how to count package id in column package then group by month and get total based on package id, i know it's little bit confusing but the result should be like below :

enter image description here

Upvotes: 1

Views: 119

Answers (2)

Prix
Prix

Reputation: 19528

You could try using SUM and CASE:

SELECT DATE_FORMAT(sf.date_start, '%d-%m-%Y') as `Month`,
       SUM(CASE WHEN sf.package = 1 THEN 1 ELSE 0 END) AS `Package 1`,
       SUM(CASE WHEN sf.package = 2 THEN 1 ELSE 0 END) AS `Package 2`,
       SUM(CASE WHEN sf.package = 3 THEN 1 ELSE 0 END) AS `Package 3`,
       SUM(CASE WHEN sf.package = 4 THEN 1 ELSE 0 END) AS `Package 4`
FROM `stores_profile` sp
LEFT JOIN `stores_fee` sf ON sp.id = sf.store_id 
WHERE sf.date_start >= '".$filter_date."' GROUP BY MONTH(sf.date_start)

Another way I can think of would be doing sub queries to it.

Upvotes: 2

Stephan
Stephan

Reputation: 8090

If i understood correctly your requirements you need a pivot like :

SELECT 
    SUM(IF(sf.package=1,1,0)) as package_1,
    SUM(IF(sf.package=2,1,0)) as package_2,
    SUM(IF(sf.package=3,1,0)) as package_3,
    SUM(IF(sf.package=4,1,0)) as package_4,
    DATE_FORMAT(sf.date_start, '%d-%m-%Y') as `month`
 FROM 
    `stores_profile` sp
 LEFT JOIN  `stores_fee` sf 
    ON sp.id=sf.store_id 
 WHERE 
    sf.date_start >= '".$filter_date."' 
 GROUP BY 
    MONTH(sf.date_start)

Upvotes: 1

Related Questions