0

I have a table in a mysql db as

id  fruit   number_eaten   day   
----------------------------------
1  apple        2           1
2  banana       1           1
3  orange       3           2
4  apple        1           2
5  banana       2           3
6  peach        1           3

I'm trying to figure out how to select such that I can compare how many was eaten per day and place into a spreadsheet so i get

fruit     number_eaten_day_1   number_eaten_day_2    number_eaten_day_3
------------------------------------------------------------------------
apple             2                  1                      0
banana            1                  0                      2
orange            0                  3                      0
peach             0                  0                      1
dardub
  • 3,166
  • 5
  • 29
  • 31
  • 1
    See http://stackoverflow.com/questions/5826455/join-two-tables-with-a-1-m-relationship-where-the-second-table-needs-to-be-fla, http://stackoverflow.com/questions/6158230/mysql-pivot-table-with-dynamic-headers-based-on-single-column-data & http://stackoverflow.com/questions/5997710/mysql-pivot-table-problem – The Scrum Meister Jul 19 '11 at 22:47
  • While you can accomplish this in SQL, remember that the database is there to store data, and the application is there to format it for output. Make the simple select. It won't take much code to do that formatting. – Dan Grossman Jul 19 '11 at 22:49
  • @Dan, I figured using sql would make grouping the fruit names together easier. – dardub Jul 19 '11 at 22:52
  • Easier than one line of application code? `$data[$row['fruit']][$row['day']] = $row['number_eaten']`. In PHP, that within the loop over the results of a simple `SELECT * FROM table` would produce the structure of your spreadsheet. – Dan Grossman Jul 19 '11 at 22:54
  • @Dan, don't mean to argue, but I'm not using php at all for this. So now I have to start writing a script in php for this purpose, which would be more than one line of code in practicality. – dardub Jul 19 '11 at 22:58
  • It was just an example. You can do the same in whatever language is producing your spreadsheet. – Dan Grossman Jul 20 '11 at 00:41

1 Answers1

1

it's easier to have separate row per fruit and day with summed value number_eaten:

select fruit, day, sum(number_eaten)
from fruits_eaten_by_day 
group by fruit, day

but it should also be possible to have exact result you need by doing this:

select 
  fruit, 
  sum(if(day=1, number_eaten, 0)) as number_eaten_day_1, 
  sum(if(day=2, number_eaten, 0)) as number_eaten_day_2, 
  sum(if(day=3, number_eaten, 0)) as number_eaten_day_3
from fruits_eaten_by_day
group by fruit
keymone
  • 8,006
  • 1
  • 28
  • 33