HELP!

Let's say I have a table similar to the one below:
ID Car Color
1 BMW silver
2 BMW silver
3 BMW red
4 BMW red
5 BMW red
Is it possible to group BMW, and count the colors in separate fields.
Car Silver Red
BMW 2 3
"Chambers" <anonymous@.discussions.microsoft.com> wrote in message
news:9533FD53-A616-438E-BB32-5559CA6B7DDC@.microsoft.com...
> Let's say I have a table similar to the one below:
> ID Car Color
> 1 BMW silver
> 2 BMW silver
> 3 BMW red
> 4 BMW red
> 5 BMW red
> Is it possible to group BMW, and count the colors in separate fields.
> Car Silver Red
> BMW 2 3
If you have a known set of colours, then try
SELECT Car
, (SELECT COUNT(*)
FROM vehicles AS v
WHERE Colour = 'silver' and v.Car = o.Car)
AS Silver
, (SELECT COUNT(*)
FROM vehicles AS v
WHERE Colour = 'red' and v.Car = o.car)
AS Red
FROM vehicles AS o
GROUP BY Car
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
|||"Chambers" <anonymous@.discussions.microsoft.com> wrote in message
news:9533FD53-A616-438E-BB32-5559CA6B7DDC@.microsoft.com...
> Let's say I have a table similar to the one below:
> ID Car Color
> 1 BMW silver
> 2 BMW silver
> 3 BMW red
> 4 BMW red
> 5 BMW red
> Is it possible to group BMW, and count the colors in separate fields.
> Car Silver Red
> BMW 2 3
>
>
>
|||To create crosstabs both static (known number of pivot columns) and
dynamic (unknown number of pivot columns) without any complicated
sql coding check out the RAC utility for S2k.RAC is somewhat similar
to the Access crosstab but is much more powerful and has many
options.
RAC v2.2 and QALite @.
www.rac4sql.net
|||Bob, much thanks to you, the script works perfectly. I did have select within a select as you did, but you took it a step further using V and O. What is V and O by the way? Are they virtual fields?
-- Bob Simms wrote: --
"Chambers" <anonymous@.discussions.microsoft.com> wrote in message
news:9533FD53-A616-438E-BB32-5559CA6B7DDC@.microsoft.com...
> Let's say I have a table similar to the one below:
> 1 BMW silver
> 2 BMW silver
> 3 BMW red
> 4 BMW red
> 5 BMW red
> BMW 2 3
If you have a known set of colours, then try
SELECT Car
, (SELECT COUNT(*)
FROM vehicles AS v
WHERE Colour = 'silver' and v.Car = o.Car)
AS Silver
, (SELECT COUNT(*)
FROM vehicles AS v
WHERE Colour = 'red' and v.Car = o.car)
AS Red
FROM vehicles AS o
GROUP BY Car
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:338537
"Chambers" <anonymous@.discussions.microsoft.com> wrote in message
news:3564FA65-B638-415B-9806-362D5A3C8581@.microsoft.com...
> Bob, much thanks to you, the script works perfectly. I did have select
within a select as you did, but you took it a step further using V and O.
What is V and O by the way? Are they virtual fields?
No, they are just aliases for the tables
FROM vehicles AS v
means that you can then refer to the vehicles table as v
Because we are referring to the vehicles table in the outer and inner SELECT
statements, we have to have give them different aliases so SQL knows which
one we are talking about.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004