The easiest thing to do would be to create an additional table containing all the possible valid values for contract level, then join on that table to show counts. Otherwise it's just not possible to show what's not there - in your case, think of this: how would MySQL know to show 5 when there are no 5's, but not also show the count for every other integer that's not there? (6, 7, 8, .. 1048576, 1048577, etc.)

CREATE TABLE contractlevel ( level int );
then INSERT 1, 2, 3, etc.

then you need a LEFT JOIN like so:

select cl.level, count(c.level) as count
from contractlevel cl
left join contract c using (level)
group by cl.level;

Hope this helps!

Dan


Jay wrote:
Thank you, Peng Yi-fan

but incase there is no contract with the level 5, it will not be shown.
I would like to see:

level   amount
1       34
2       0
3       18
4       986
5       0

I could add it in the application, but I try to do it within the Query.

btw. the right join I mentioned, doesn't work.

Has someone another idea?

Thank you!

Jay

The schema of your contract should be like this:

Contract (id, level, ...)

where column 'id' is the primary key, isn't it?

If so, you can try this:

SELECT COUNT(id)
FROM contract GROUP BY level

----- Original Message ----- From: "Jay" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, May 11, 2006 5:41 PM
Subject: SELECT Query GROUP BY


Hello MySQL Users

I have a contract table. Each contract has a certain level, which can be
in a range from 1-5. This information is stored as a number. There is no
additional table for the levels.

I would like to get a list with the amount of contracts of each level -
including 0 for the levels with no contracts.

Until now I just used :
SELECT COUNT(*), level FROM contract GROUP BY level
but this is just showing level with contracts.

I tried a right join with a table which contains just integer values.
Seems like a workaround, but I'm interested in a easier aolution - I bet
there is one.

Thank you!

Jay

PS: I'm using Version 4.1


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to