the create table should have been
CREATE TABLE `alarms_audit` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ACTIONCODE` char(1) NOT NULL
`INSERT_TIME` datetime NOT NULL
)
Ravi Malghan Wrote:
-------------------------------------------------------
> Need some help with building a query. I have the
> following table. The field ACTIONCODE has values
> I, D, U.
>
>
> CREATE TABLE `test_table` (
> `id` bigint(20) unsigned NOT NULL
> AUTO_INCREMENT,
> `ACTIONCODE` char(1) NOT NULL
> `INSERT_TIME` datetime NOT NULL
> )
>
>
>
> I am trying to get number of I, D and U for each
> day.
>
>
> 2016-02-18 10 20 1
> 2016-02-19 3 6 8
>
>
> Maybe there is a different way to get all the
> counts for each day in a single line. The
> following query prints each count in a new line.
>
>
> select
> DATE(INSERT_TIME),
> CASE
> WHEN ACTIONCODE in ('I') THEN 'New Alarm'
> WHEN ACTIONCODE in ('U') THEN 'Update'
> WHEN ACTIONCODE in ('D') THEN 'Delete'
> ELSE 'Other'
> END AS 'Type', count(*) AS alarms
> from alarms_audit
> group by
> DATE(INSERT_TIME),
> case
> WHEN ACTIONCODE in ('I') THEN 'New Alarm'
> WHEN ACTIONCODE in ('U') THEN 'Update'
> WHEN ACTIONCODE in ('D') THEN 'Delete'
> ELSE 'Other'
> END
>