I have wrote a sql query which has join many tables and I only want to group a single column of table. And here is my slq query.
select us.Username, ro.Role, ca.CartName, ca.Date from Users us
left join Roles ro on ro.RoleID = us.RoleID
left join Carts ca on ca.UserId = us.ID
group by ca.Date;
And here is the tables' destruction:
ID Username RoleID
1 Tri 1
2 Lan 1
3 Nhu 1
4 Lac 1
5 Linhchi 2
RoleID Role Description
1 Admin Someone in administrator board
2 User Someone who has an account
3 Guess Someone who just view the website
CartId CartName CartDescription UserId Date
1 Computer Computer CMS 1 2/11/2013 12:00:00 AM
2 Desk Writing Desk 2 2/11/2013 12:00:00 AM
3 Print Paper Company's print paper 3 3/11/2013 12:00:00 AM
4 Headphone Small headphone 3 2/11/2013 12:00:00 AM
5 MP3Player MP3Player Portable 4 4/11/2013 12:00:00 AM
//DesireTable after run the query
Username Role CartName Date
Tri Admin Computer 2/11/2013 12:00:00 AM
Lan Admin Desk 2/11/2013 12:00:00 AM
Lan Admin Headphone 2/11/2013 12:00:00 AM
Nhu Admin Print Paper 3/11/2013 12:00:00 AM
Lac Admin MP3Player 4/11/2013 12:00:00 AM
So the result must group by column "Date" from "Carts" table. But the query generated an error message "Column 'Carts.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I got stuck in current context, I need your help to solve this issue.
In my issue, I only want to group one single column "Date" but if I insert only one column Date in group by statement, it will generate an error. Thus, I will insert all column from joined tables into group by clause.
select us.Username, ro.Role, ca.CartName, ca.Date from Users us left join Roles ro on ro.RoleID = us.RoleID left join Carts ca on ca.UserId = us.ID group by ca.Date, us.Username, ro.Role, ca.CartName;
The trick to group only one column which I want to group is put that column in the first place of group by clause, so that it will group that column first and then the other later. By doing this way I can control the group information as I want.