Sub total of one group needed within totals of other sub groups

Transact-SQL
1

I am trying to create a subtotal of a specific product type while still getting the individual totals for all the other products. For example, I have a table that lists all the product types with a group name for each product.

Group Name ProductTypeCode
Car CA_ELEC
Car CA_GAS
Car CA_DIESEL
Car CA_HYBRID
Truck TK_4x4
Truck TK_4x2
Truck TK_2Door
Truck TK_4Door
Truck TK_Dump
Van Panel
Van HighTop

I need to get the count of each of the different types of cars, like
Car CA_ELEC 25
Car CA_GAS 100
Car CA_DIESEL 40
Car CA_HYBRID 5
but I need a total for all trucks, like

Truck 500
and then continue with the other types, like

Van Panel 50
Van HighTop 25
where 500 is the total for all trucks categories added together.

I'm not sure how to get the count for just the trucks and the individual counts for each of the other individual types.

Thanks for any help anyone can provide and hope this makes sense

2

please always provide sample data as follows otherwise folks might not respond if they have to do that little extra leg work for you.
Will this work?

create table #bill_the_kid([Group Name] varchar(50), ProductTypeCode varchar(50))

 insert into #bill_the_kid
select 'Car', 'CA_ELEC' UNION
select 'Car', 'CA_GAS' UNION
select 'Car', 'CA_DIESEL' UNION
select 'Car', 'CA_HYBRID ' UNION
select 'Truck', 'TK_4x4' UNION
select 'Truck', 'TK_4x2' UNION
select 'Truck', 'TK_2Door' UNION
select 'Truck', 'TK_4Door' UNION
select 'Truck', 'TK_Dump' UNION
select 'Van', 'Panel' UNION
select 'Van', 'HighTop' 

select '', [Group Name], COUNT(*) bill
  from #bill_the_kid
  where [Group Name] = 'Truck'
  group by [Group Name]
union
select [Group Name], ProductTypeCode, COUNT(*) bill
  from #bill_the_kid
  where [Group Name] <> 'Truck'
  group by ProductTypeCode, [Group Name]
  order by 2 asc


  drop table #bill_the_kid
3

yosiasz:
Thanks and sorry for not providing enough information. A Union was what I was what I needed and that did the trick. Thanks again for your extra efforts

4

Or, if the UNION/double(or more) scan(s) of the main table(if you have additional combinations) causes performance issues for you, this instead:

select [Group Name], ProductTypeCode, COUNT(*) bill
from #bill_the_kid
group by [Group Name], ProductTypeCode with rollup
having ([Group Name] <> 'Truck' AND ProductTypeCode is not null)
    or ([Group Name] = 'Truck' AND ProductTypeCode is null)
1 Like
5

Scott:
Thanks, I'll try that also