how to get total ticket count on days wise using mysql

Pls Provide some sample data ? or can you explain me

1day      3days        5days
 3                 6                10
or
total ticket count on days?

group name      ticketid              date      
      
L1_R          39108             2008-12-31 11:55:30
L1_R          39986             2009-01-03 06:05:53
L2_NET        40090             2009-01-03 13:47:35
L1_NET      42646             2009-01-10 17:00:31
L1_R          43955             2009-01-15 11:28:29



all the tickets count should be based on group name
order by groupname is needed

and the expected out for the data you mentioned above?

L1_R          39108             2016-11-03 11:55:30
L1_R          39986             2016-11-03 06:05:53
L1_R          43955             2016-10-31 11:28:29


1DAY   >=CURRENTDATE-1DAY
3DAYS  >=CURRENTDATE-3DAYS
5DAYS  >=CURRENTDATE-5DAYS

L1_R          3(TOTAL)         2(1D)          2(3D)        3(5D)

Here it is ..

CREATE TABLE tickets
(
	groupname varchar(100),
	ticketid int ,			
	createdtime datetime
)
GO

INSERT INTO tickets VALUES
('L1_R'      ,    39108      ,       '2016-11-02 11:55:30'),
('L1_R'      ,    39986     ,        '2016-11-02 06:05:53'),
('L2_NET'    ,    40090     ,        '2009-01-02 13:47:35'),
('L1_NET'    ,    42646     ,        '2009-01-10 17:00:31'),
('L1_R'      ,    43955     ,        '2016-10-31 11:28:29')

GO

SELECT t.groupname, COUNT(*) cnt 
, COUNT ( CASE WHEN DATEDIFF(NOW(),createdtime) >= 1  AND DATEDIFF(NOW(),createdtime) < 3 THEN 1 END ) [1Day]
, COUNT ( CASE WHEN DATEDIFF(NOW(),createdtime) >= 3  AND DATEDIFF(NOW(),createdtime) < 5 THEN 1 END ) [3Day]
, COUNT ( CASE WHEN DATEDIFF(NOW(),createdtime) >= 5 THEN 1 END ) [5Day]
FROM tickets t
GROUP BY t.groupname
ORDER BY t.groupname

--

Select allOpen in new window

Output
--------
groupname      cnt      1Day      3Day      5Day
L1_NET               1          0                   0               1
L1_R               3          2                   1                 0
L2_NET              1          0                   0                1

Hope it helps !

I am looking for count of ticket ids.
i didn't understand which count we are calculating here..
COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END )
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END )
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 5 THEN 1 END )


the above not giving any result

Post your entire query you are executing?

query:
======
SELECT ag.group_name, COUNT(*) cnt
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END )
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END )
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 5 THEN 1 END )
from tickets io
left outer join group_master ag on ag.id = io.assigned_to_group
left outer join statusmaster st on st.status_id = io.status_id
GROUP BY ag.group_name;


Result:
=======
group_name: Support
                                                                                                 cnt: 18
COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END ): 0
COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END ): 0
                                      COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 5 THEN 1 END ): 18

                                                                                          group_name: ONSITE
                                                                                                 cnt: 5
COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END ): 0
COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END ): 0
                                      COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 5 THEN 1 END ): 5

ohh my bad try this

CREATE TABLE tickets
(
	groupname varchar(100),
	ticketid int ,			
	createdtime datetime
)
GO

INSERT INTO tickets VALUES
('L1_R'      ,    39108      ,       '2016-11-02 11:55:30'),
('L1_R'      ,    39986     ,        '2016-11-02 06:05:53'),
('L2_NET'    ,    40090     ,        '2009-01-02 13:47:35'),
('L1_NET'    ,    42646     ,        '2009-01-10 17:00:31'),
('L1_R'      ,    43955     ,        '2016-10-31 11:28:29')

GO

SELECT t.groupname, COUNT(*) cnt 
, SUM ( CASE WHEN DATEDIFF(NOW(),createdtime) >= 1  AND DATEDIFF(NOW(),createdtime) < 3 THEN 1 END ) [1Day]
, SUM ( CASE WHEN DATEDIFF(NOW(),createdtime) >= 3  AND DATEDIFF(NOW(),createdtime) < 5 THEN 1 END ) [3Day]
, SUM ( CASE WHEN DATEDIFF(NOW(),createdtime) >= 5 THEN 1 END ) [5Day]
FROM tickets t
GROUP BY t.groupname
ORDER BY t.groupname

--

Select allOpen in new window

WHATEVER U POSTED THAT IS WORKING FINE

IN MY ACTUAL QUERY I HAVE ADDED ONE LEFT OUTER JOIN

PLEASE VERIFY MY ACTUAL QUERY
I DIDN'T UNDERSTAND WHY IT IS NOT WORKING
I TRIED WITH SUM AND COUNT AS WELL

QUERY:
SELECT ag.group_name, COUNT(*) cnt
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END ) as 1day
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END ) as 3day
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 5 THEN 1 END ) as 5day
from tickets io
left outer join group_master ag on ag.id = io.assigned_to_group
GROUP BY ag.group_name
ORDER BY ag.group_name;

Are you getting error or incorrect output ?  Query look ok to me.

You want records that are present in Tickets table only ?

Are you getting error or incorrect output ?  Query look ok to me.

no error

result is:

 group_name               | cnt    | 1day | 3day | 5day   |
+--------------------------+--------+------+------+--------+
|  TCLMUM-BKC              |      1 |    0 |    0 |      1 |
| Amada_Accel              |      2 |    0 |    0 |      2 |
| Calcom Project           |      1 |    0 |    0 |      1 |
| Engagement Managers      |     34 |    0 |    0 |     34 |

if u see above result cnt column values 5day column values are same


You want records that are present in Tickets table only ?

group name i am getting from another table group_master

left outer join group_master ag on ag.id = io.assigned_to_group

Ok try below 2 options...

Try 1

SELECT ag.group_name, COUNT(*) cnt 
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END ) as 1day
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END ) as 3day
, COUNT(CASE WHEN DATEDIFF(NOW(),created_time) = 5 THEN 1 END ) as 5day
from tickets io 
LEFT OUTER JOIN group_master ag on ag.id = io.assigned_to_group 
GROUP BY ag.group_name
ORDER BY ag.group_name;

Select allOpen in new window

Try 2...

--

SELECT ag.group_name, COUNT(*) cnt 
, SUM(CASE WHEN DATEDIFF(NOW(),created_time) >= 1  AND DATEDIFF(NOW(),created_time) < 3 THEN 1 END ) as 1day
, SUM(CASE WHEN DATEDIFF(NOW(),created_time) >= 3  AND DATEDIFF(NOW(),created_time) < 5 THEN 1 END ) as 3day
, SUM(CASE WHEN DATEDIFF(NOW(),created_time) = 5 THEN 1 END ) as 5day
from tickets io 
LEFT OUTER JOIN group_master ag on ag.id = io.assigned_to_group 
GROUP BY ag.group_name
ORDER BY ag.group_name;

 

Select allOpen in new window

Hope it helps!!

Hi srikotesh,

Is it done ?

Regards,
Pawan

same query only I have posted in my last comment

I have tried with both sum,count

is there any difference?

same query only I have posted in my last comment

I have tried with both sum,count

is there any difference?

same query only I have posted in my last comment

I have tried with both sum,count

is there any difference?

NOW I am getting the below result:



 group_name               | cnt    | 1day | 3day | 5day   |
+--------------------------+--------+------+------+--------+
|  TCLMUM-BKC              |    150|    1 |    0 |      0 |


but ticket creation time 2nd november it should be in 3day coulmn
bcoz ticket is not created on yesterday

| group_name | ticket_id   | created_time        |
+------------+-------------+---------------------+
| TCLMUM-BKC |     2755329 | 2016-11-02 16:45:09 |
+------------+-------------+-------------------

Hi srikotesh,

Todays date is  is 2016-11-04.

Now 2016-11-04 - 2016-11-02 is 2 so why it should be in 3days section.

For 3days  we should have the difference = 3. right ?

that logic is fine...

i have created new ticket with current time.
this should come in 1day column i am not seeing that data
bcoz 1day column is taking only previous day information only it is not taking newly created tickets into 1day column

+---------------+-------------+---------------------+
| group_name    | incident_id | created_time        |
+---------------+-------------+---------------------+
| LINUX         |     2755327 | 2016-11-08 09:29:08 |

i have changed the condition like below
COUNT(CASE WHEN DATEDIFF(NOW(),created_time) <3 THEN 1 END ) as 1day
it is working now

Great that its working !!

Superb