MySQL

counting possible values of one column

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: May 2007

Posts: 52

Reputation: hashinclude is an unknown quantity at this point

Solved Threads: 0

hashinclude hashinclude is offline

Offline

Junior Poster in Training

counting possible values of one column

Jul 21st, 2009

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

•

Join Date: Sep 2008

Posts: 119

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: counting possible values of one column

Jul 21st, 2009

do you mean something like this

 Help with Code Tags 
 SQL Syntax (Toggle Plain Text) 
SELECT field2,count(Field1)
FROM Table
GROUP BY field2
Select field2,count(Field1)
From Table
Group by field2

Last edited by cgyrob; Jul 21st, 2009 at 2:14 pm.

•

Join Date: Sep 2008

Posts: 119

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: counting possible values of one column

Jul 21st, 2009

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT field2, sum(field1)
FROM Table
GROUP 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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT field2, field1, count(*)
FROM table
GROUP BY field2, field1
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.

Last edited by cgyrob; Jul 21st, 2009 at 3:54 pm.

•

Join Date: May 2007

Posts: 52

Reputation: hashinclude is an unknown quantity at this point

Solved Threads: 0

hashinclude hashinclude is offline

Offline

Junior Poster in Training

Re: counting possible values of one column

Jul 21st, 2009

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 ?

Attached Thumbnails

•

Join Date: Sep 2008

Posts: 119

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: counting possible values of one column

Jul 21st, 2009

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

Last edited by cgyrob; Jul 21st, 2009 at 7:14 pm.

•

Join Date: May 2007

Posts: 52

Reputation: hashinclude is an unknown quantity at this point

Solved Threads: 0

hashinclude hashinclude is offline

Offline

Junior Poster in Training

Re: counting possible values of one column

Jul 21st, 2009

•

Originally Posted by cgyrob

What is the table schema?

see attachment

Attached Thumbnails

•

Join Date: Sep 2008

Posts: 119

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: counting possible values of one column

Jul 21st, 2009

Try this

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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

•

Join Date: May 2007

Posts: 52

Reputation: hashinclude is an unknown quantity at this point

Solved Threads: 0

hashinclude hashinclude is offline

Offline

Junior Poster in Training

Re: counting possible values of one column

Jul 22nd, 2009

•

Originally Posted by cgyrob

Try this
Help with Code Tags

sql Syntax (Toggle Plain Text)
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
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