My mistakes

Archive for the ‘Mysql’ Category

Posted on: April 25, 2014

MySQL distinct count if conditions unique

Build a query to find distinct women and men in a given records. 
The row is identified by the field id and it may be duplicated , but the gender for an id should only be counted once.

    COUNT(DISTINCT CASE WHEN gender ='m' THEN id END) as male_count,  
    COUNT(DISTINCT CASE WHEN gender ='f' THEN id END) as female_count
FROM table.

create table test_table(id integer unsigned,cName varchar(50));

insert into test_table values(1,’test1′);
insert into test_table values(2,’test2′);
insert into test_table values(3,’test3′);

select group_concat(cName separator ‘,’)
from test_table

| group_concat(cName separator ‘,’) |
| test1,test2,test3 |

But now,I want to limit the group_concat to the first two rows of the result:

| group_concat(name separator ‘,’) |
| test1,test2 |

Solution :
We cannot use the limit with the group_concat so the better solution is to go for SUBSTRING_INDEX like

SUBSTRING_INDEX(group_CONCAT(cName) , ‘,’, 2) as cName from test_table


First, get the tablenames:

SELECT table_name,null FROM information_schema.tables

Second, get the columnnames:

SELECT column_name,null FROM information_schema.columns WHERE table_name = ‘tablename’

for more information use the below link