MySQL to PHP array grouping duplicate values

Please be gentle with me as I am kind of learning as I go using manuals, trial and error and (ahem) reverse engineering of open source software.

I Have a MySQL query that returns a table like the following (the query is already quite complex as it includes sums and counts from joined tables):

   DATE      ANSWER   CATEGORY    COUNT
2011-01-01      Y       CAT1       22
2011-01-01      Y       CAT2       220
2011-01-01      N       CAT1       14
2011-01-01      N       CAT2       530
2011-01-02      Y       CAT1       50
2011-01-02      Y       CAT2       270
2011-01开发者_JAVA百科-02      N       CAT1       18
2011-01-02      N       CAT2       576

I need to get everything onto single lines with date such as

   DATE      Total Y     Total N     Total Cat1    Total Cat2    Total Overall
2011-01-01     242         544           36            750            786
2011-01-02     320         594           68            750            846

Now I assume to get to these figures i'm going to need to group them into a multi-dimensional array so that I can play with the figures like so:

Array
(
 2011-01-01 => Array (
    Y => Array(
        [CAT1] = 22
        [CAT2] = 220
        )
    N => Array(
        [CAT1] = 14
        [CAT2] = 530
        )
     )
 2011-01-02 => Array (
    Y => Array(
        [CAT1] = 50
        [CAT2] = 270
        )
    N => Array(
        [CAT1] = 18
        [CAT2] = 576
        )
     )
)

But this is where I get stuck I can't seem to figure out the foreach loops to get the data into an array in this instance and once it's there, how do I display it in a table?

Any help would be much appreciated.

---

SELECT
  DATE,
  SUM(CASE ANSWER WHEN 'Y' THEN COUNT ELSE 0 END) AS TotalY,
  SUM(CASE ANSWER WHEN 'N' THEN COUNT ELSE 0 END) AS TotalN,
  SUM(CASE CATEGORY WHEN 'CAT1' THEN COUNT ELSE 0 END) AS TotalCat1,
  SUM(CASE CATEGORY WHEN 'CAT2' THEN COUNT ELSE 0 END) AS TotalCat2,
  SUM(COUNT) AS TotalOverall
FROM subquery
GROUP BY DATE

---

You could get the results from the query itself.

I know it's a not the best query but it's to give you a idea about how to solve what you want

SELECT theDate AS `DATE`, 
    (SELECT SUM(theCount) FROM st WHERE theAnswer = 'Y' AND theDate = a.theDate) AS 'Total Y',
    (SELECT SUM(theCount) FROM st WHERE theAnswer = 'N' AND theDate = a.theDate) AS 'Total N',
    (SELECT SUM(theCount) FROM st WHERE theCategory = 'CAT1' AND theDate = a.theDate) AS 'Total Cat1',
    (SELECT SUM(theCount) FROM st WHERE theCategory = 'CAT2' AND theDate = a.theDate) AS 'Total Cat2',
    (SELECT SUM(theCount) FROM st WHERE theCategory IN('CAT1','CAT2') AND theDate = a.theDate) AS 'Total Overall'
FROM st AS a
GROUP BY theDate

Results:

   DATE      Total Y     Total N     Total Cat1    Total Cat2    Total Overall
2011-01-01     242         544           36            750            786
2011-01-02     320         594           68            846            914

---

You could pull the data you want directly from the SQL database.

Try :

SELECT `DATE`, ANSWER, SUM(`COUNT`) as TOTAL 
FROM table -- Change this!
GROUP BY `DATE`, ANSWER

And replace ANSWER by CATEGORY to get the total by category.