counting possible values of one column

[hashinclude]

Hello,

please bear with me if this question is stupid.
I'm fairly new to this and i really need to know. is it possible to use "SELECT COUNT" to get the number of DIFFERENT values one column currently holds grouped by a value from another column.?

[cgyrob]

do you mean something like this

Select field2,count(Field1)
From Table
Group by field2

[cgyrob]

Since you didn't give too much information I thought of a few other interpetations of what you asked.

If field1 is a numeric value you might want the sum of the field grouped by field2

Select field2, sum(field1)
From Table
Group by field2

or you might want to know how many records of field1 there are that are also rouped by field2

Select field2, field1, count(*)
From table
Group by field2, field1

I'm sure one of the 3 syntax's will work for what you are asking.

[hashinclude]

Thank you for replying. let me explain with an example :)
let's say i have this table: (check attachment please)
i want to get a count for example like this :
CASE1:

cars|---------------|color red
bmw|---------------------------|1
audi|----------------------------|0
ford|-----------------------------| 1
toyota|---------------------------|0

OR CASE2:

cars|---------------|car count
bmw|----------------------------|3
audi|-----------------------------|2
ford|------------------------------| 1
toyota|--------------------------|1

in the first case the count returns possible values (cars) based on a condition (red cars)
Or in the second case the count returns how the number of different values assigned to the column "car" (as in how many is bmw and how many is audi...etc)

can this be done with a single SELECT query ?

[cgyrob]

Nevermind, I take it the attachment is the table schema.

Is it important to do it all in one query. What are you using the resultset for because if you do it in one query you will most likely get back something like the following.

BMW 2 RED 1
BMW 2 BLACK 1
AUDI 8 RED 3
AUDI 8 BLACK 2
AUDI 8 BLUE 3
etc....

[hashinclude]

What is the table schema?

see attachment

[cgyrob]

Try this

SELECT MAKE.MAKE, MAKE.count, color.Color, color.count 
FROM(
       SELECT MAKE, count(*)count
       FROM cars 
       Group by MAKE
    )MAKE,
   (   SELECT MAKE, color, count(*)count
       From cars
       Group by MAKE,color
   )color
Where make.make = color.make

[hashinclude]

Try this

SELECT MAKE.MAKE, MAKE.count, color.Color, color.count 
FROM(
       SELECT MAKE, count(*)count
       FROM cars 
       Group by MAKE
    )MAKE,
   (   SELECT MAKE, color, count(*)count
       From cars
       Group by MAKE,color
   )color
Where make.make = color.make

sorry for the late reply....
although your code didn't quite work.. it did direct me to the right way in solving my problem so thanks you my friend :).

problem solved.
added to your rep. :)

[cgyrob]

Im glad i could atleast point you in the right direction. good luck with the rest.