0

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.?

2
Contributors
8
Replies
9
Views
8 Years
Discussion Span
Last Post by cgyrob
0

do you mean something like this

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

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.

0

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 ?

Attachments untitled.jpg 18.04 KB
0

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....

1

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
Votes + Comments
Very helpful
0

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. :)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.