CODEWITHSUNDEEP
phpmysql
Linu S
Linu S

Reputation: 43

Group by multiple columns with join

I have three tables named bus, bustype, depo. The bus table contains bus name and foreign key from bustype (bustypeid) and depo(depoid). Bustypes are SF,FP,LS etc and depo table contains different depos.

I need to get the list of buses grouped by depos and again group by bustype.

table - bus

busid   busname   depoid    bustypeid   
    1      KL-15 4567   7      1             
    2      KL-04-54     4      1             
    3      KL-02-11     7      3             
    4      KL-02-15     7      2

table - bustype

bustypeid   busname   
    1           SF           
    2           FP             
    3           LS

table - depo

 depoid       deponame
   7             depo1
   4             depo2

I wrote the query to group by bustypeid, that is

SELECT COUNT(busid) as availability,BS.bustype, B.depoid FROM `bus` as B inner join bustype as BS on B.`bustypeid`=BS.bustypeid GROUP BY BS.bustype

Now I need to again group by its by depo too. The result might looks like , 

availability    bustype depoid  
1                FP     7
1                LS     4
2                SF     7

Upvotes: 0

Views: 40

Answers (1)

Jens
Jens

Reputation: 69440

Simply add it to the group by clause:

 .... GROUP BY BS.bustype, depoid

Upvotes: 2

Related Questions