CODEWITHSUNDEEP
mysqlcount
Rukikun
Rukikun

Reputation: 291

Count by String and grouping by hour

I have a problem regarding with one table in my database and I have to query the table.

I want to get the Count of ACTIVATED from Card Status and group them by desired branch and by hour as well...

Can you help me in getting my query to work?

Here's my query:

SELECT
    DATE_FORMAT(DATE_VERIFIED, '%H') AS hour,
    COUNT(CARD_STATUS) AS California
FROM customer
WHERE
    CARD_STATUS = 'ACTIVATED' AND 
    DESIRED_BRANCH = 'California'
GROUP BY hour

My query can only get California... I want to also get the other cities.

Here's the table.

enter image description here

Here is the desired output

enter image description here

Upvotes: 1

Views: 66

Answers (3)

Sergej Panic
Sergej Panic

Reputation: 839

SELECT active_california_hours.hour, COUNT(*) 
FROM (  SELECT DATE_FORMAT(DATE_VERIFIED, '%H') as `hour` 
    FROM `customer` 
    where   
    CARD_STATUS = 'ACTIVATED' 
    and DESIRED_BRANCH = 'California') active_california_hours
GROUP BY hour

Upvotes: 0

cuongtd
cuongtd

Reputation: 3192

you get only California because DESIRED_BRANCH = 'California'.if you want get count of actived Card_Status group by hour and desired branch your query must look like 

SELECT DATE_FORMAT(DATE_VERIFIED, '%H') as `hour`, COUNT(CARD_STATUS) as 
`Number_Active` FROM `customer` where CARD_STATUS = 'ACTIVATED' 
group by `hour`,branch

Upvotes: 1

Rajeev Ranjan
Rajeev Ranjan

Reputation: 4106

You are probably going wrong in mentioning DESIRED_BRANCH = 'California' in the where clause. You query should look like below

SELECT DATE_FORMAT(DATE_VERIFIED, '%H') as `hour`, DESIRED_BRANCH, COUNT(CARD_STATUS) as 
`Count` FROM `customer` where CARD_STATUS = 'ACTIVATED' group by `hour`,DESIRED_BRANCH

This would be grouping by hour as well as DESIRED_BRANCH as required. You would have to get DESIRED_BRANCH as well if it is to be included in the group by clause.

Upvotes: 0

Related Questions