CODEWITHSUNDEEP
mysqlsqlselect
user3466409
user3466409

Reputation: 13

Mysql query on a single table

I have a transaction table with datetime, type, measure. I want to produce the day, count, count with measure>20 for type=12.

Not sure how to go about it.

To get the day, count, type I'd write select date(datetime), count from table where type=12 group by date(datetime)

Just not sure how to add the 3rd col (count with measure>20).

I've thought of trying a left self join, or a corelated subquery.

Appreciate any advice.

Table Name Alerts
Col1 Alert_time (datetime)
Col 2 Alert_type (integer)
Col 3 Measure (integer)

Sample data
2015/01/20 9:00|12|10
2015/01/20 8:00|12|30
2015/01/20 7:00|12|40
2015/01/21 5:00|13|30
2015/01/21 8:00|12|10

Desired Output
2015/01/20|3|2
2015/01/21|1|0

Upvotes: 0

Views: 43

Answers (2)

Dipen Adroja
Dipen Adroja

Reputation: 425

You can have you query like this.

SELECT DATE(datetime), COUNT(*), COUNT(CASE WHEN measure > 20 THEN 1 ELSE 0 END) FROM mytable WHERE type = 12 GROUP BY date(datetime)

Upvotes: 0

Mureinik
Mureinik

Reputation: 311188

You could perform a count operation on a case expression:

SELECT   DATE(datetime),
         COUNT(*), 
         COUNT(CASE WHEN measure > 20 THEN 1 ELSE NULL END)
FROM     mytable
WHERE    type = 12 
GROUP BY date(datetime)

Upvotes: 1

Related Questions