CODEWITHSUNDEEP
mysql
Yadi_Muyadi
Yadi_Muyadi

Reputation: 21

How to make aliases sql queries with certain criteria

I Have wrote sql query something like this :

SELECT `petugas_input`, 
COUNT(`petugas_input`) AS `01-MAR`,
COUNT(`petugas_input`) AS `02-MAR`,
COUNT(`petugas_input`) AS `03-MAR`
FROM `tabel_arsip`
WHERE `tgl_input_arsip`>='2016-03-01 00:00:00' AND `tgl_input_arsip`<='2016-03-01 23:59:59'
GROUP BY `petugas_input`

and its generate result like this

sample_table

My question is how to add criteria to the aliases column so that it will show different value on different date. (not the same value in the date column as above)

Upvotes: 2

Views: 40

Answers (3)

itzmukeshy7
itzmukeshy7

Reputation: 2677

Try this one:

SELECT `petugas_input`, 
COUNT(CASE WHEN DATE(tgl_input_arsip) = '2016-03-01' THEN petugas_input ELSE 0 END) AS `01-MAR`, 
COUNT(CASE WHEN DATE(tgl_input_arsip) = '2016-03-02' THEN petugas_input  ELSE 0 END) AS `02-MAR`, 
COUNT(CASE WHEN DATE(tgl_input_arsip) = '2016-03-03' THEN petugas_input ELSE 0 END) AS `03-MAR`
FROM `tabel_arsip`
WHERE `tgl_input_arsip`>='2016-03-01 00:00:00' AND `tgl_input_arsip`<='2016-03-03 23:59:59'
GROUP BY `petugas_input`;

:)

Upvotes: 0

1000111
1000111

Reputation: 13519

You should not think for these hard-coded column aliases rather make a query for each petugas_input and for each date (within the given date range) along with the count.

Something like this:

SELECT 
`petugas_input`, 
DATE(`tgl_input_arsip`) `date`,
COUNT(*) total
FROM `tabel_arsip`
WHERE `tgl_input_arsip`>='2016-03-01 00:00:00' AND `tgl_input_arsip`<='2016-03-01 23:59:59'
GROUP BY `petugas_input`,`date`;

And you will get the following output structure:

petugas_input           date               total

A                    yyyy-mm-dd             n1
B                    yyyy-mm-dd             n2

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80629

You'd have to rely on a little complex grouping:

SELECT 
    `petugas_input`, 
    SUM(CASE WHEN DATE(tgl_input_arsip) = '2016-03-01' THEN 1 ELSE 0 END) AS `01-MAR`, 
    SUM(CASE WHEN DATE(tgl_input_arsip) = '2016-03-02' THEN 1 ELSE 0 END) AS `02-MAR`, 
    SUM(CASE WHEN DATE(tgl_input_arsip) = '2016-03-03' THEN 1 ELSE 0 END) AS `03-MAR`,
FROM `tabel_arsip`
WHERE `tgl_input_arsip`>='2016-03-01 00:00:00' AND `tgl_input_arsip`<='2016-03-01 23:59:59'
GROUP BY `petugas_input`

Upvotes: 1

Related Questions