x
Solved

# how to get total ticket count on days wise using mysql

Posted on 2016-11-03
Medium Priority
106 Views
HI Experts,

tickets table

ticket id,
created time,
ticket type,
groupname

groupname  total_tickets       1day      3days        5days

test101            10                      3                 6                10

how to get total ticket count on days wise using mysql.
0
Question by:srikotesh
• 13
• 11

LVL 38

Expert Comment

ID: 41871759
Pls Provide some sample data ? or can you explain me

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

LVL 2

Author Comment

ID: 41871813
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
0

LVL 38

Expert Comment

ID: 41871817
and the expected out for the data you mentioned above?
0

LVL 2

Author Comment

ID: 41871862
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)
0

LVL 38

Expert Comment

ID: 41871882
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

--
``````

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 !
0

LVL 2

Author Comment

ID: 41871999
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
0

LVL 38

Expert Comment

ID: 41872012
Post your entire query you are executing?
0

LVL 2

Author Comment

ID: 41872047
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
0

LVL 38

Expert Comment

ID: 41872216

``````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

--
``````
0

LVL 2

Author Comment

ID: 41873419
WHATEVER U POSTED THAT IS WORKING FINE

IN MY ACTUAL QUERY I HAVE ADDED ONE LEFT OUTER JOIN

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;
0

LVL 38

Expert Comment

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

You want records that are present in Tickets table only ?
0

LVL 2

Author Comment

ID: 41873461
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
0

LVL 38

Expert Comment

ID: 41873466
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;
``````

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;

``````

Hope it helps!!
0

LVL 38

Expert Comment

ID: 41873516
Hi srikotesh,

Is it done ?

Regards,
Pawan
0

LVL 2

Author Comment

ID: 41873543
same query only I have posted in my last comment

I have tried with both sum,count

is there any difference?
0

LVL 2

Author Comment

ID: 41873544
same query only I have posted in my last comment

I have tried with both sum,count

is there any difference?
0

LVL 2

Author Comment

ID: 41873545
same query only I have posted in my last comment

I have tried with both sum,count

is there any difference?
0

LVL 38

Accepted Solution

Pawan Kumar earned 2000 total points
ID: 41873550
No I have changed below , Try it out...

, SUM(CASE WHEN DATEDIFF(NOW(),created_time) = 5 THEN 1 END ) as 5day
from tickets io

``````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;
``````
0

LVL 2

Author Comment

ID: 41873735
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 |
+------------+-------------+-------------------
0

LVL 38

Expert Comment

ID: 41873843
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 ?
0

LVL 2

Author Comment

ID: 41878129
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 |
0

LVL 2

Author Comment

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

LVL 38

Expert Comment

ID: 41878144
Great that its working !!
0

LVL 2

Author Closing Comment

ID: 41879971
Superb
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.