The number of enumerated values?

Question

If my table looks like this:

CREATE TABLE `daily_individual_tracking` (
  `daily_individual_tracking_id` int(10) unsigned NOT NULL auto_increment,
  `daily_individual_tracking_date` date NOT NULL default ''0000-00-00'',
  `sales` enum(''no'',''yes'') NOT NULL COMMENT ''no'',
  `repairs` enum(''no'',''yes'') NOT NULL COMMENT ''no'',
  `shipping` enum(''no'',''yes'') NOT NULL COMMENT ''no'',
  PRIMARY KEY  (`daily_individual_tracking_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

mostly fields can be yes or no.

How can I calculate how many yes for each column in a date range?

Thank!!

Answer 1

You can either run three queries like this:

SELECT COUNT(*)
FROM daily_individual_tracking
WHERE sales = 'YES'
AND daily_individual_tracking_date BETWEEN '2010-01-01' AND '2010-03-31' 

Or, if you want, you can get all three at once:

SELECT (
    SELECT COUNT(*)
    FROM daily_individual_tracking
    WHERE sales = 'YES'
    AND daily_individual_tracking_date BETWEEN '2010-01-01' AND '2010-03-31'
) AS sales_count, (
    SELECT COUNT(*)
    FROM daily_individual_tracking
    WHERE repairs = 'YES'
    AND daily_individual_tracking_date BETWEEN '2010-01-01' AND '2010-03-31'
) AS repairs_count, (
    SELECT COUNT(*)
    FROM daily_individual_tracking
    WHERE shipping = 'YES'
    AND daily_individual_tracking_date BETWEEN '2010-01-01' AND '2010-03-31'
) AS shipping_count

Another way to do this is to use SUM instead of COUNT. You can also try this to see how this affects performance:

SELECT
    SUM(sales = 'YES') AS sales_count,
    SUM(repairs = 'YES') AS repairs_count,
    SUM(shipping = 'YES') AS shipping_count
FROM daily_individual_tracking
WHERE daily_individual_tracking_date BETWEEN '2010-01-01' AND '2010-03-31'