CODEWITHSUNDEEP
mysql
Sema Rathod
Sema Rathod

Reputation: 3

Select with count

So.. here is the thing.. Im trying to get a report based on this table on Mysql.

Table have these columns

statusvalue submitdate          employeeNumber
2           2016-11-17 17:49:05     2
1           2016-11-17 17:49:11     4
3           2016-11-17 17:49:16     4
4           2016-11-17 17:50:58     2
5           2016-11-17 17:51:07     5



Status 0 = Pending
1= Started
2=inprogress
3= cancelled
4= terminated
5=unknown

now i need to select data as below

date           day    pending  started  inprogress cancelled  terminated total 
2017-04-17     Monday   0        1          4        0         1           6    
2017-04-16     Sunday   0        1          4        0         1           6

so far i can get the date and the day

 select  date(submitdate) as subdate, case weekday(date(submitdate))
 WHEN 0 then 'PENDING'
        WHEN 1 then 'STARTED'
        WHEN 2 then 'INPROGRESS'
        WHEN 3 then 'CANCELLED'
        WHEN 4 then 'TERMINATED'
        WHEN 5 then 'UNKNOWN' END as submitdate
from tb_status t1 where employeenumber=15 group by subdate order by subdate ;

however not quite sure how to do a count based on status.. any help would be greatly appreciated.

Upvotes: 0

Views: 31

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

You just need a basic pivot query here. Aggregate over the submission date, and then tally the various types of status as you go along.

SELECT
    DATE(submitdate) AS subdate,
    WEEKDAY(submitdate) AS day,
    SUM(CASE WHEN WEEKDAY(DATE(submitdate)) = 0 THEN 1 END) AS PENDING,
    SUM(CASE WHEN WEEKDAY(DATE(submitdate)) = 1 THEN 1 END) AS STARTED,
    SUM(CASE WHEN WEEKDAY(DATE(submitdate)) = 2 THEN 1 END) AS INPROGRESS,
    SUM(CASE WHEN WEEKDAY(DATE(submitdate)) = 3 THEN 1 END) AS CANCELLED,
    SUM(CASE WHEN WEEKDAY(DATE(submitdate)) = 4 THEN 1 END) AS TERMINATED,
    SUM(CASE WHEN WEEKDAY(DATE(submitdate)) = 5 THEN 1 END) AS UNKNOWN
FROM tb_sattus t1
WHERE employeenumber = 15
GROUP BY DATE(submitdate)
ORDER BY subdate

Note that your current query is not correct, because you are selecting string literals based on the status. Instead, in my query the various status types appear as column names, with the counts you want instead being selected.

Upvotes: 1

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

Give this a try:

SELECT
    `submitDate` as `date`,
    DATE_FORMAT(`submitDate`,'%a') as `day`,
    SUM(if(`statusvalue` = 0,1,0)) as `pending`,
    SUM(if(`statusvalue` = 1,1,0)) as `started`,
    SUM(if(`statusvalue` = 2,1,0)) as `inprogress`,
    SUM(if(`statusvalue` = 3,1,0)) as `cancelled`,
    SUM(if(`statusvalue` = 4,1,0)) as `terminated`,
    SUM(if(`statusvalue` = 5,1,0)) as `unknown`,
    SUM(if(NOT `statusvalue` IN (0,1,2,3,4,5),1,0)) as `invalid`,
    COUNT(`statusvalue`) as `total`
FROM `tbstatus`
GROUP BY `submitDate`
ORDER BY `submitDate`;

Upvotes: 0

Related Questions