Yuval Eliav Yuval Eliav - 7 months ago 9
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

SELECT
CustomersGroups.idCustomerGroup , Customers.type , COUNT(*)
FROM
CustomersGroups
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

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;