SQL Group By

SQL Group By Example One

Q: Using the movies Something About Mary, The Cable Guy, and The Mask - how many of these three films were the thespians who were in these films in.

   
 Select Count(Film_Title), Thespian_First_Name, Thespian_Last_Name
   From Thespian_Film_Table
  Where Film_Title in ("Something About Mary", 
                       "The Cable Guy", 
                       "The Mask")
     Group by Thespian_Last_Name;

   
 1,    Matthew,    Broderick
 2,    Jim,        Carrey
 2,    Cameron,    Diaz
 2,    Ben,        Stiller

Note: Group by can only be used with an aggregate function - Avg(), Count(), Count (Distinct), Max(), Min(), or Sum().

Also Note: You can see the syntax for in on our in page.

...And Special Thanks: to Gert for catching a bug on this page.

SQL Group By Example Two using Having

Q: Now show the same thing with thespians who have been in more than one of the three films.

   
 Select Count(Film_Title), Thespian_First_Name, Thespian_Last_Name
   From Thespian_Film_Table
  Where Film_Title in ("Something About Mary", 
                       "The Cable Guy", 
                       "The Mask")
     Group by Thespian_Last_Name
     Having Count(Thespian_Last_Name) > 1;

   
 2,    Jim,        Carrey
 2,    Cameron,    Diaz
 2,    Ben,        Stiller

Note: Like Group by, Having can only be used with an aggregate function - Avg(), Count(), Count (Distinct), Max(), Min(), or Sum().

Also Note: You can see the syntax for in on our in page.

Comments Comments are left by visitors to FluffyCat.com and may or may not be accurate.

Comment by Larry on 2011-05-12 Rate this Comment

Once approach you can use is to create a view of your table with month as a field, then use that field for your group by.

Comment by archive on 2007-04-09 Rate this Comment

Dear Sir,
I am a software developer and using IB 6.5. I need information about the "extract" keyword. My goal is to obtain result sum, count or avg etc. grouped by ie month numbers or years like
YEAR MONTH TOTALSALES
2004 1 34
2004 2 23
2004 3 55
.
.

In IB SQL, we have a usage like: "select distinct extract(month from saledate) from salestable" But you know we cannot group by aggregate columns. If you can help me I will be happy.