Yuval Eliav Yuval Eliav - 7 months ago 13
SQL Question

how to get a name from a group by id in sql

I have a table of customers groups and I would like to select the group's names (and another value called Notes) but group by doesn't allow me to do that even though the entire group has the same value of names and notes.

select cg.idCustomerGroup,
sum(case when c.type = 'child' then 1 else 0 end) as Children,
sum(case when c.type = 'adult' then 1 else 0 end) as Adults,
sum(case when c.type = 'senior' then 1 else 0 end) as Seniors
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

Answer

You have to include the group's name and notes in the GROUP BY:

select cg.idCustomerGroup, cg.Name, cg.Notes,
       sum(case when c.type = 'child' then 1 else 0 end) as Children,
       sum(case when c.type = 'adult' then 1 else 0 end) as Adults,
       sum(case when c.type = 'senior' then 1 else 0 end) as Seniors
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, cg.Name, cg.Notes