I recently set up a MYSQL database connected to a form filled with checkboxes. If the checkbox was selected, it would insert into the associated column a value of '1'; otherwise, it would receive a value of '0'.
I'd like to eventually look at aggregate data from this form, and was wondering if there was any way I could use MYSQL to get a number for each column which would be equal to the number of rows that had a value of '1'.
I've tried variations of:
select count(*) from POLLDATA group by column_name
which was unsuccessful, and nothing else I can think of seems to make sense (admittedly, I'm not all too experienced in SQL).
I'd really like to avoid:
select count(*) from POLLDATA where column_1='1'
for each column (there close to 100 of them). Is there any way to do this besides typing out a select count(*) statement for each column?
If it helps, the columns are 'artist1', 'artist2', ....'artist88', 'gender', 'age', 'city', 'state'. As I tried to explain below, I was hoping that I'd be able to do something like:
select sum(EACH_COLUMN) from POLLDATA where gender='Male', city='New York City';
(obviously EACH_COLUMN is bogus)
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `POLLDATA` ADD INDEX `polldata_idx_column_name` (`column_name`);
SELECT count(*) FROM POLLDATA GROUP BY POLLDATA.column_name ORDER BY NULL