1

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-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.

3 Answers 3

1
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
1
  • Nice, didn't think of using the CASE +1 much nicer than all my sub selects Commented May 19, 2011 at 19:49
1

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
1
  • Thanks for taking the time to answer Phill. Your solution also works well but I settled for Andriy's in the end.
    – Leseul
    Commented May 20, 2011 at 9:35
0

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.

2
  • Many apologies - looks like my question was too vague - the SQL query is about as complex as it can be as it's alerady including sums and counts of values from multiple seperate tables. Plus would this give me my categories?
    – Leseul
    Commented May 19, 2011 at 17:33
  • Really, look at group by as Leseul suggests. DBMS+proper-SQL-query is designed to return results in the way you want, why reinvent the wheel in your PHP code.
    – user140327
    Commented May 19, 2011 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.