NYCPHP Meetup

[Michael Myers]

I have a primary data table that holds the same type of results for two 
different categories. There is a column in the table that reflects the 
category for each row.

Up to this point, I have been creating two separate summary tables, one 
for each category. Is it possible to combine them in one 
CREATE...SELECT construct? I would add a suffix to the column names to 
distinguish which category the data came from.

The sql below generates the summary data for the category where species 
= 'human'.  Can I do the same for an additional species and put 
everything into one summary table?

DROP TABLE IF EXISTS primary_dr_hum_summ;
CREATE TABLE primary_dr_hum_summ
SELECT id, ROUND(avg(IC50), 4) AS avg_IC50, COUNT(IC50) as count,
   ROUND(MIN(IC50), 4) AS min_IC50, ROUND(MAX(IC50), 4) AS max_IC50,
   ROUND(STD(IC50), 4) AS std_dev, ROUND(max(IC50)/min(IC50)) as ratio
FROM primary_dr
WHERE species = "human"
GROUP BY id ORDER BY avg_IC50;
ALTER TABLE primary_dr_hum_summ ADD PRIMARY KEY (id);