Combining 2 values in Group By Field on Sum Query

I've got a table that has 3 fields: CarModel, CarColor and
CarRegistration. I want to summarize the number of records by CarModel
and CarColor. Easy with a sum query:

Group By fields CarColor and CarRegistration and do a count on
CarRegristration.

But what if I want to combine 2 car colors, eg. Red and Blue? I want
the count of all the other colors still, but just want Red and Blue
count combined. Any way to do this with one query?

-- Thanks for looking, Paul

Re: Combining 2 values in Group By Field on Sum Query

select carmodel, carcolor, count(*) cnt from tbl1
where carcolor not in ('red', 'blue')
Union All
Select carmodel, 'Red and Blue', cnt(*) cnt from tbl1
where carcolor in ('red', 'blue')
group by carmodel

This is basically 2 queries rolled up into 1 using the "Union All"
statement.

The first query counts each carmodel by color and excludes colors that
are red or blue.

The second query only counts car models from the set of cars that are
either red or blue, and I added a lable 'Red and Blue' -- in order to
match the column count of the first query. The catch with Union All is
that all the queries must contain the same number of columns.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Combining 2 values in Group By Field on Sum Query

select carmodel, carcolor, count(*) cnt from tbl1
where carcolor not in ('red', 'blue')
Group By carmodel, carcolor
Union All
Select carmodel, 'Red and Blue', cnt(*) cnt from tbl1
where carcolor in ('red', 'blue')
group by carmodel



Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Combining 2 values in Group By Field on Sum Query

Thank you, Rich. With a few small changes to your code and applied to
a different table I got exactly what I'd hoped for. Thanks! -- Paul
Cross

SELECT tblClaims.AdjusterID, tblClaims.ClaimTypeID, COUNT(*) As Cnt
from tblClaims
WHERE tblClaims.ClaimTypeID not in (1, 2)
GROUP BY tblClaims.AdjusterID, tblClaims.ClaimTypeID
UNION ALL SELECT tblClaims.ClaimTypeID, '1 and 2', COUNT(*) As Cnt
from tblClaims
WHERE tblClaims.AdjusterID in (1, 2)
GROUP BY tblClaims.AdjusterID,tblClaims.ClaimTypeID;


On Oct 18, 12:34 pm, Rich P wrote:
> select carmodel, carcolor, count(*) cnt from tbl1
> where carcolor not in ('red', 'blue')
> Group By carmodel, carcolor
> Union All
> Select carmodel, 'Red and Blue', cnt(*) cnt from tbl1
> where carcolor in ('red', 'blue')
> group by carmodel
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***