Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Count enumerated values?

Tags:

enums

mysql

count

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

basically the fields can be either yes or no.

How can I count how many yes's their are for each column over a date range?

Thanks!!

like image 944
JD Isaacks Avatar asked Apr 07 '10 20:04

JD Isaacks


People also ask

What is enumerate value?

An enumeration is a data type that consists of a set of named values that represent integral constants, known as enumeration constants. An enumeration is also referred to as an enumerated type because you must list (enumerate) each of the values in creating a name for each of them.

How do you count enums?

Use the len() class to get the number of elements in an enum, e.g. len(Color) . The len() function returns the length (the number of items) of an object and can directly be passed an enum. Copied! The len() function returns the length (the number of items) of an object.

What is the example of enumerated data type?

An enumerated type is a type whose legal values consist of a fixed set of constants. Common examples include compass directions, which take the values North, South, East and West and days of the week, which take the values Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday.

What are enumeration variables?

A variable of the enumeration type stores one of the values of the enumeration set defined by that type. Variables of enum type can be used in indexing expressions and as operands of all arithmetic and relational operators.


1 Answers

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 like this:

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 it is to use SUM instead of COUNT. You could try this too to see how it affects the 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'
like image 117
Mark Byers Avatar answered Oct 14 '22 18:10

Mark Byers