Need Output like mentioned below in MySQL + PHP

Question

Hi please watch the image link below for table structure. http://i31.tinypic.com/2v0zw4o.jpg

we have to print it in a layout format like below.....like for d same date diff regions with total count....

Date      CentroOeste Nordeste Norte RJ_ES_MG SaoPaulo Sul  
7/6/2010  233         204      154   130      128      99  
7/7/2010  0           0        0     0        3        0  
7/8/2010  0           0        0     0        0        13  
7/16/2010 0           0        0     300      0        0  
8/6/2010  0           0        0     0        3        43  
Total     233         204      154   430      134      155  

How can I achieve this? Please provide me the stored procedure or query to get the desired output like this.

Answer 1

Try this (not tested):

SELECT
    DATE_FORMAT(unique_timestamps.statstimestamp, '%d/%m/%Y') AS 'Date',
    (SELECT SUM(count) FROM table WHERE region = 'CentroOeste' AND statstimestamp = unique_timestamps.statstimestamp) AS 'CentroOeste',
    (SELECT SUM(count) FROM table WHERE region = 'NordOeste' AND statstimestamp = unique_timestamps.statstimestamp) AS 'NordOeste',
    (SELECT SUM(count) FROM table WHERE region = 'Norte' AND statstimestamp = unique_timestamps.statstimestamp) AS 'Norte',
    (SELECT SUM(count) FROM table WHERE region = 'RJ_ES_MG' AND statstimestamp = unique_timestamps.statstimestamp) AS 'RJ_ES_MG',
    (SELECT SUM(count) FROM table WHERE region = 'SaoPaulo' AND statstimestamp = unique_timestamps.statstimestamp) AS 'SaoPaulo',
    (SELECT SUM(count) FROM table WHERE region = 'Sul' AND statstimestamp = unique_timestamps.statstimestamp) AS 'Sul'
FROM
    (SELECT DISTINCT statstimestamp FROM table) unique_timestamps;

Answer 2

SELECT
    DATE_FORMAT(statstimestamp, '%d/%m/%Y') AS 'Date',
    SUM((region = 'CentroOeste')*count) AS 'CentroOeste',
    SUM((region = 'NordOeste')*count) AS 'NordOeste',
    SUM((region = 'Norte')*count) AS 'Norte',
    SUM((region = 'RJ_ES_MG')*count) AS 'RJ_ES_MG',
    SUM((region = 'SaoPaulo')*count) AS 'SaoPaulo',
    SUM((region = 'Sul')*count) AS 'Sul',
    SUM(count) as 'SubTotal'
FROM
    table
group by 1
union all
SELECT
  'Total',
    SUM((region = 'CentroOeste')*count) AS 'CentroOeste',
    SUM((region = 'NordOeste')*count) AS 'NordOeste',
    SUM((region = 'Norte')*count) AS 'Norte',
    SUM((region = 'RJ_ES_MG')*count) AS 'RJ_ES_MG',
    SUM((region = 'SaoPaulo')*count) AS 'SaoPaulo',
    SUM((region = 'Sul')*count) AS 'Sul',
    SUM(count)
FROM
   table;

I've added another column, 'subtotal', to give a total by day, and also on the last row, a grand total.

Update: I've made some corrections after first posting the query, it's tested on a similar structure, it has to work.