Ad:

MySQL Discussion Thread
Marked Solved
Views: 968

Jul 21st, 2009

counting possible values of one column

Expand Post »

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

Similar Threads

read a column of text from a file in java in Java
Update the null values of a particular column in MS SQL
how to change values of particular column in datagrid... in VB.NET
assing table values to an array in JSP
Column number in Pascal and Delphi

hashinclude

Reputation Points: 11

Solved Threads: 8

Junior Poster

Offline

111 posts
since May 2007

Permalink

Jul 21st, 2009

Re: counting possible values of one column

do you mean something like this

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.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Jul 21st, 2009

Re: counting possible values of one column

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

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

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.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Jul 21st, 2009

Re: counting possible values of one column

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:

Quote ...

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

OR CASE2:

Quote ...

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

Click image for larger version

Name: untitled.jpg
Views: 49
Size: 18.0 KB
ID: 10965

hashinclude

Reputation Points: 11

Solved Threads: 8

Junior Poster

Offline

111 posts
since May 2007

Permalink

Jul 21st, 2009

Re: counting possible values of one column

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.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Jul 21st, 2009

Re: counting possible values of one column

Click to Expand / Collapse Quote originally posted by cgyrob ...

What is the table schema?

see attachment

Attached Thumbnails

Click image for larger version

Name: untitled.jpg
Views: 47
Size: 18.0 KB
ID: 10966

hashinclude

Reputation Points: 11

Solved Threads: 8

Junior Poster

Offline

111 posts
since May 2007

Permalink

Jul 21st, 2009

Re: counting possible values of one column

Try this

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

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Jul 22nd, 2009

Re: counting possible values of one column

Click to Expand / Collapse Quote originally posted by cgyrob ...

Try this
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

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.

hashinclude

Reputation Points: 11

Solved Threads: 8

Junior Poster

Offline

111 posts
since May 2007

Permalink

Jul 23rd, 2009

Re: counting possible values of one column

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

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Need some input

Next Thread in MySQL Forum Timeline: Connect to MYSQL database on separate server with PHP

DaniWeb Message

Cancel Changes

User Name
Password