Yuval Eliav Yuval Eliav - 2 years ago 59
SQL Question

how to include several count functions and group byes in one line

I am trying to get the number of customers by their types and groups all in line as such:

GroupName | GroupNotes | Count(Type1) | Count(Type2) | Count(Type3)

but instead I can only get the groupid ,the typeid and the number of types in the group by using the following query

CustomersGroups.idCustomerGroup , Customers.type , COUNT(*)
inner Join CustomersInGroup on CustomersGroups.idCustomerGroup = CustomersInGroup.idCustomerGroup
inner Join Customers on Customers.idCustomer = CustomersInGroup.idCustomer
Group by
CustomersGroups.idCustomerGroup, Customers.type

is there a way to show them in a single line , (and show the name of the group?)

Answer Source

This is a "pivot" query. Some databases directly support pivot syntax. In all, you can use conditional aggregation.

Perhaps more importantly, you should learn to use table aliases. These make queries easier to write and to read:

select cg.idCustomerGroup, 
       sum(case when c.type = 'Type1' then 1 else 0 end) as num_type1,
       sum(case when c.type = 'Type2' then 1 else 0 end) as num_type2,
       sum(case when c.type = 'Type3' then 1 else 0 end) as num_type3
from CustomersGroups cg inner Join
     CustomersInGroup cig
     on cg.idCustomerGroup = cig.idCustomerGroup inner Join
     Customers c
     on c.idCustomer = cig.idCustomer
Group by cg.idCustomerGroup;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download