0

Basically what I am trying to do is show the results from my database, broken up under the heading of the month they belong to, I have done a few searches and tried the examples, but I just can't get it working perfectly, what I am trying to get it to do is display like this...

March
Result 1
Result 2

April
Result 3

May
Result 4
Result 5
Result 6

Using advice in another thread I created a "months" table listing the months, I have grouped with my results table which looks like this.

tid
start_date
end_date
data

The best I can do is get it to display the month names, but it will only show 1 result per month, instead of up to 2 - 4 results.

Here is the query I am using on my local machine to test it.

"SELECT trips.*, months.* 
FROM trips
RIGHT OUTER JOIN months ON months.month = monthname(trips.start_date) WHERE trips.tid = '$tid' GROUP BY monthname(trips.start_date) ORDER BY trips.start_date");

If someone could point out what I am doing wrong it would be much appreciated.

JCain
  • 49
  • 7
  • make a sql fiddle - show us your table and tell us which fields you want – niyou Mar 31 '15 at 06:26
  • 1
    what is result 1, result 2 ? – US-1234 Mar 31 '15 at 06:27
  • They were just examples of how I wanted to display the records. They are records that have a start_date of that month. – JCain Mar 31 '15 at 06:30
  • GROUP BY without any aggregate functions? How come? Show us input data (i.e. table data), and expected result. – jarlh Mar 31 '15 at 06:32
  • Sorry I am still learning PHP/SQL so don't understand the question, basically the data is just start/end date, and record name, it's being used like event calendar. – JCain Mar 31 '15 at 06:36

1 Answers1

0

You should aggregate all fields which are not grouped.

SELECT sum(trips.data), months.month 
FROM trips
    RIGHT OUTER JOIN months ON months.month = monthname(trips.start_date) WHERE trips.tid = '$tid' 
GROUP BY months.month 
ORDER BY trips.start_date
niyou
  • 875
  • 1
  • 11
  • 23