MySQL - Get total count of specific IDs

Question

I have a MySQL table like this:

 MYTABLE |-------------| | ID | COLOR | |----|--------| | 01 | WHITE | | 01 | BLACK | | 01 | YELLOW | | 02 | RED | | 02 | BLUE | | 02 | YELLOW | | 03 | BLACK | | 03 | BLUE | | 03 | YELLOW | |-------------|

When I want to get all colors from an ID, I execute this query:

SELECT `COLOR` FROM `MYTABLE` WHERE `ID` = 2 RESULT |--------| | COLOR | |--------| | RED | | BLUE | | YELLOW | |--------|

How can I also get how many times each color exist in table?
What query I have to execute to get the following results?

HOW CAN I GET THIS? |----------------| | COLOR | COUNT | |--------|-------| | RED | 1 | | BLUE | 2 | | YELLOW | 3 | |----------------|

Thanks in advance.

Abhik Chakraborty · Accepted Answer · 2014-05-05 08:42:35Z

SELECT COLOR, count(*) as `COUNT` From MYTABLE GROUP BY COLOR;

You can add where condition with Where ID = 2 before group by if there multiple colors available for each ID

Now if you need to find the total time each color appear in the table irrespective of the id and then filter data with ID then you can use the following

select t1.COLOR, cnt as `count` from MYTABLE t1 inner join ( select COLOR, count(*) as cnt from MYTABLE group by COLOR )t2 on t2.COLOR = t1.COLOR where t1.id = 2

DEMO

The first query has count=1 on all rows. I need the total count. The second query return this error: Can't reopen table: 't1'
not sure about the error you are getting its working for me sqlfiddle.com/#!2/fbfe82/5
Sorry! I tested it using CREATE TEMPORARY TABLE. How stupid am I? It is working excellent! THANKS!

Collectives™ on Stack Overflow

MySQL - Get total count of specific IDs

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related