PHP/MySQL: Is there a better way to count distinct values in a column
I have a table with 10 columns, and about 20,000 rows. I want to get the count of the distinct values in 5 of the columns, so normally, I would probably write 5 queries thus: SELECT column_a, count(column_a) FROM my_table GROUP BY column_a; SELECT column_b, count(column_b) FROM my_table GROUP BY column_b; SELECT column_c, count(column_c) FROM my_table GROUP BY column_c; SELECT column_d, count(column_d) FROM my_table GROUP BY column_d; SELECT column_e, count(column_e) FROM my_table GROUP BY column_e; My question is, is there a way I can do this in a more generic/future proof way? Maybe a stored procedure which I just pass the table name to? Or some PHP function which I just give the name of the table to? Ultimate goal is to write just one query to get the above data.
Hie i had the same issue on Monday but i used a view to kill it see if it might help 1) Create a View (my_table1 - 5 ) which pulls all the data you want in the World. Yote Yote 2) Use Below to Sum and Arrange from the View select `viewalltables`.`Products` AS `Products`, sum((case when (`viewalltables`.`priority` = 'Immediate') then 1 else 0 end)) AS `A`, sum((case when (`viewalltables`.`priority` = 'Urgent') then 1 else 0 end)) AS `B`, sum((case when (`viewalltables`.`priority` = 'High') then 1 else 0 end)) AS `C`, sum((case when (`viewalltables`.`priority` = 'Normal') then 1 else 0 end)) AS `D`, sum((case when (`viewalltables`.`priority` = 'Low') then 1 else 0 end)) AS `E`, sum((case when (`viewalltables`.`name` = 'New') then 1 else 1 end)) AS `F`, sum((case when (`viewalltables`.`name` = 'Closed') then 1 else 0 end)) AS `G`, sum((case when (`viewalltables`.`name` = 'Resolved') then 1 else 0 end)) AS `H` from `viewalltables` group by `viewalltables`.`Products`; 3) i think it can cook well On Thu, Aug 1, 2013 at 9:31 AM, Peter Karunyu <pkarunyu@gmail.com> wrote:
I have a table with 10 columns, and about 20,000 rows.
I want to get the count of the distinct values in 5 of the columns, so normally, I would probably write 5 queries thus:
SELECT column_a, count(column_a) FROM my_table GROUP BY column_a; SELECT column_b, count(column_b) FROM my_table GROUP BY column_b; SELECT column_c, count(column_c) FROM my_table GROUP BY column_c; SELECT column_d, count(column_d) FROM my_table GROUP BY column_d; SELECT column_e, count(column_e) FROM my_table GROUP BY column_e;
My question is, is there a way I can do this in a more generic/future proof way?
Maybe a stored procedure which I just pass the table name to? Or some PHP function which I just give the name of the table to?
Ultimate goal is to write just one query to get the above data.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
participants (2)
-
Benjamin -
Peter Karunyu