CODEWITHSUNDEEP
phpmysql
Bruno Luiz K.
Bruno Luiz K.

Reputation: 178

How to count BOOLEAN columns MySQL

I have a table with a column name "IS_QUICK", type TINYINT (0|1);

I need to count how much times he is true (1) and false (0) for a period:

In MySQL:

    SELECT DIA, MES, HORA, ANO, QUICK, NOT_QUICK FROM (
    SELECT * ,(
        SELECT COUNT(rq1.IS_QUICK) FROM qp1_relatorio_quickview rq1 where rq1.IS_QUICK = 1 AND rq1.created_at BETWEEN '2018-07-03 00:00:00' AND '2018-07-06 23:59:59'
    ) as QUICK,
    (
        SELECT COUNT(rq1.IS_QUICK) FROM qp1_relatorio_quickview rq1 where rq1.IS_QUICK = 0 AND rq1.created_at BETWEEN '2018-07-03 00:00:00' AND '2018-07-06 23:59:59'
    ) as NOT_QUICK
   , YEAR(rq.created_at) as ANO
   , MONTH(rq.created_at) as MES
   , DAY(rq.created_at) as DIA
   , HOUR(rq.created_at) as HORA
 FROM qp1_relatorio_quickview rq WHERE rq.created_at BETWEEN '2018-07-03 00:00:00' AND '2018-07-06 23:59:59'

) as relatorio
GROUP BY DIA

But he return count of all IS_QUICK between day 3 and 6, not for day 3 only (for example)

Edit: table:

    CREATE TABLE `qp1_relatorio_quickview` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `IS_QUICK` TINYINT(1) NOT NULL DEFAULT '0',
    `PRODUTO_ID` BIGINT(20) NOT NULL DEFAULT '0',
    `PRODUTO_VARIACAO_ID` BIGINT(20) NOT NULL DEFAULT '0',
    `QUANTIDADE` INT(11) NOT NULL DEFAULT '0',
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    PRIMARY KEY (`ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=19
;

Upvotes: 2

Views: 1076

Answers (2)

DarthJDG
DarthJDG

Reputation: 16591

To count a boolean TINYINT field, you can simply use SUM(field) to count 1s, or SUM(NOT field) to count 0s.

SELECT
    SUM(IS_QUICK) AS QUICK,
    SUM(NOT IS_QUICK) AS NOT_QUICK
FROM qp1_relatorio_quickview rq
WHERE rq.created_at BETWEEN '2018-07-03 00:00:00' AND '2018-07-06 23:59:59';

The above query will select the total for the period. If you want it summarised per day for example, make sure you select and group by all relevant fields:

SELECT
    YEAR(rq.created_at) as ANO,
    MONTH(rq.created_at) as MES,
    DAY(rq.created_at) as DIA,
    SUM(IS_QUICK) AS QUICK,
    SUM(NOT IS_QUICK) AS NOT_QUICK
FROM qp1_relatorio_quickview rq
WHERE rq.created_at BETWEEN '2018-07-03 00:00:00' AND '2018-07-06 23:59:59'
GROUP BY ANO, MES, DIA;

Upvotes: 3

Oleksandr Pobuta
Oleksandr Pobuta

Reputation: 405

Hi can you try to use sum 

  SELECT
    sum(CASE WHEN rq1.IS_QUICK= 1 THEN 1 ELSE 0 END) as IS_QUICK,
    sum(CASE WHEN rq1.IS_QUICK= 0 THEN 1 ELSE 0 END) as NOT_QUICK,
   , YEAR(rq.created_at) as ANO
   , MONTH(rq.created_at) as MES
   , DAY(rq.created_at) as DIA
   , HOUR(rq.created_at) as HORA
 FROM qp1_relatorio_quickview rq WHERE rq.created_at BETWEEN '2018-07-03 00:00:00' AND '2018-07-06 23:59:59'

Upvotes: 0

Related Questions