SET @START='{STARTDATE}', @END='{ENDDATE}', @DATE='', @HOUR='', @TYPE='';


SELECT
gg.TYPE,
gg.HOUR,
SUM(gg.TOTAL) TOTAL,
SUM(gg.SUN) SUN,
SUM(gg.MON) MON,
SUM(gg.TUE) TUE,
SUM(gg.WED) WED,
SUM(gg.THU) THU,
SUM(gg.FRI) FRI,
SUM(gg.SAT) SAT
FROM
        (SELECT
        g.HOUR,
        g.TYPE,
        COUNT(g.TYPE) 'TOTAL',
        IF(DAYOFWEEK(g.DATE)=1,COUNT(g.TYPE),0) SUN,
        IF(DAYOFWEEK(g.DATE)=2,COUNT(g.TYPE),0) MON,
        IF(DAYOFWEEK(g.DATE)=3,COUNT(g.TYPE),0) TUE,
        IF(DAYOFWEEK(g.DATE)=4,COUNT(g.TYPE),0) WED,
        IF(DAYOFWEEK(g.DATE)=5,COUNT(g.TYPE),0) THU,
        IF(DAYOFWEEK(g.DATE)=6,COUNT(g.TYPE),0) FRI,
        IF(DAYOFWEEK(g.DATE)=7,COUNT(g.TYPE),0) SAT
        FROM
                (SELECT
                @DATE := DATE(ci.POSTDATE) DATE,
                @HOUR := CONCAT(IF(HOUR(ci.POSTDATE)<10,CONCAT('0',HOUR(ci.POSTDATE)),HOUR(ci.POSTDATE)),':00') HOUR,
                @TYPE := CASE
                        WHEN ci.DETAILS LIKE 'EFT%' THEN 'MEMBER'
                        WHEN ci.DETAILS LIKE 'Prepaid%' THEN 'MEMBER'
                        WHEN ci.DETAILS LIKE '%punches%' THEN 'PUNCH CARD'
                        ELSE 'GUEST'
                END TYPE,
                CONVERT(CONCAT(@DATE, @TYPE, @HOUR),CHAR) GROUPID
                FROM
                checkins AS ci
                WHERE
                DATE(ci.POSTDATE) >= @START AND
                DATE(ci.POSTDATE) <= @END
                ORDER BY
                DATE(ci.POSTDATE) ASC,
                CASE
                        WHEN ci.DETAILS LIKE 'EFT%' THEN 'MEMBER'
                        WHEN ci.DETAILS LIKE 'Prepaid%' THEN 'MEMBER'
                        WHEN ci.DETAILS LIKE '%punches%' THEN 'PUNCH CARD'
                        ELSE 'GUEST'
                END ASC,
                CONCAT(HOUR(ci.POSTDATE),':00') ASC) g
        GROUP BY
        g.GROUPID) gg
GROUP BY
CONCAT(gg.TYPE,gg.HOUR)