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]
