SELECT COUNT(UNIQUE(column)) ?????

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

SELECT COUNT(UNIQUE(column)) ?????

Post by Skara »

This is maybe a little hard to explain..
I have many many entries with an (int)`status` column. I want to know if I can select the count of different types of statuses. In other words...
The entries are: 1,6,2,4,3,2,3,2,4,4,5,5,7,8,7
I want the results to be:
(1,1)
(6,1)
(2,3)
(4,3)
(3,2)
(5,2)
(7,2)
(8,1)
-- I think I counted those right. :P

In other words, I don't want to have to select ALL the status rows and add them up myself.
(current solution:)

Code: Select all

SELECT `status`;
loop {
    $status[`status`]++;
}
better solution?:
I want to know "How many of each of the different status values are there?"
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SELECT COUNT(UNIQUE(column)) ?????

Post by Eran »

Code: Select all

SELECT `status`,COUNT(`id`) AS count FROM `table_name` GROUP BY `status`
Replace 'id' with your primary key name if it is different.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: SELECT COUNT(UNIQUE(column)) ?????

Post by Weirdan »

I want to know "How many of each of the different status values are there?"
If you only want a count (but not the specific statuses available) you may want to use a simpler query:
[sql] SELECT count(DISTINCT `status`) FROM `table_name` [/sql]
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Re: SELECT COUNT(UNIQUE(column)) ?????

Post by Skara »

Aha... yeah, the first reply is what I need. Thanks, both!!
Post Reply