MySql version 8.0.17 average datediff

Databases
1

Hi all,

In the table stored an a database MySql version 8.0.17 I have these four field set as datetime type

+---------------------+-----------+---------------------+------------+
| tdate               | tigx      | tclosed             | tcompleted |
+---------------------+-----------+---------------------+------------+
  • the field tdate never null ;
  • the field tigx it could be null ;
  • the field tclosed it could be null ;
  • the field tcompleted it could be null ;

Now I get the average datediff respecting this sequence

  1. If tclosed IS NOT NULL AND tcompleted IS NOT NULL AND tigx IS NOT NULL the AVG is ROUND(AVG(DATEDIFF(tclosed, tdate)),1)
  2. If tclosed IS NULL AND tcompleted IS NULL AND tigx IS NULL the AVG is ROUND(AVG(DATEDIFF(CURDATE(), tdate)),1)

But if at least one of the fields tigx or tclosed or tcompleted is null I have to take the first populated field from tigx or tclosed or tcompleted ā€¦ ā€¦ and using this first populated field for get the average datediff.

Here I am stuckā€¦ any suggestion?

My query below

SELECT
    tkt,
    tdate,
    tstate,
    tigx,
    tclosed,
    tcompleted,
CASE        
        WHEN ( tclosed IS NOT NULL AND tcompleted IS NOT NULL AND tigx IS NOT NULL ) THEN
        ROUND( AVG( DATEDIFF( tclosed, tdate )), 1 ) 
        WHEN ( tclosed IS NULL AND tcompleted IS NULL AND tigx IS NULL ) THEN
        ROUND( AVG( DATEDIFF( CURDATE(), tdate )), 1 ) 
    END AS `avg` 
FROM
    `tbl_c` 
WHERE
    NOT ( `tkt` LIKE '%I%' OR `tkt` LIKE '%L%' AND tstate IN ( 'Closed' ) AND tigx IS NULL AND tclosed IS NULL AND tcompleted IS NULL ) 
ORDER BY
    tdate DESC;
2

Question: can tigx be null if tclosed is not null? can tclosed be null if tcompleted is not null?
(In other words: Are these cascading conditions?)

1 Like
3

the answer you seek will probably best be provided by judicious construction of a COALESCE function, listing columns in the appropriate sequence

what do you mean by average? you use AVG() which is an aggregate function, but youā€™re not aggregatingā€¦ did you want to aggregate by date? average for all tickets for each date?

if so, you cannot show the individual row details, just the date and the aggregate(s)

p.s. you are going to have problems mixing ORs and ANDs that way

1 Like
4

thanks for help, really appreciated.

solved using COALESCE function your suggestion

SELECT
	tkt,
	tdate,
	tstate,
	tigx,
	tclosed,
	tcompleted,
CASE
		
		WHEN ( tclosed IS NOT NULL AND tcompleted IS NOT NULL AND tigx IS NOT NULL ) THEN
		ROUND( AVG( DATEDIFF( tclosed, tdate )), 1 ) 
		WHEN ( tclosed IS NULL AND tcompleted IS NULL AND tigx IS NULL ) THEN
		ROUND( AVG( DATEDIFF( CURDATE(), tdate )), 1 ) ELSE ROUND(
			AVG(
			DATEDIFF( COALESCE ( tclosed, tcompleted, tigx ), tdate )),
			1 
		END AS `avg` 
	FROM
		`tbl_c` 
	WHERE
		NOT ( `tkt` LIKE '%I%' OR `tkt` LIKE '%L%' AND tstate IN ( 'Closed' ) AND tigx IS NULL AND tclosed IS NULL AND tcompleted IS NULL ) 
	GROUP BY
		tkt 
ORDER BY
	tdate DESC;
5

actually your entire CASE expression can be done with one COALESCE

if you GROUP BY tkt then you cannot show tdate, tstate, tigx, tclosed, or tcompleted, nor can you sort by tdate ā€“ on MySQL version 8 you should be getting an ā€œonly full group byā€ error

also, please look at your ORs and ANDs again

1 Like
6

Things that could benefit from parenthesis includeā€¦

Alsoā€¦ wait up.

tigx IS NULL AND tclosed IS NULL AND tcompleted IS NULL

If your where condition is looking only where those are (EDIT: NOTā€¦) NULL, why do we need a case for if they arenā€™t are?

(Edit: Negation screwing me up. but same question.)

1 Like
7

Not are cascade conditionā€¦

8

Iā€™m sorry, error copy/paste

this is the last sql, the GROUP BY is for tarea and not for tkt

what do you mean with

actually your entire CASE expression can be done with one COALESCE

SELECT
	tarea, 
    tkt,
	tdate,
	tstate,
	tigx,
	tclosed,
	tcompleted,
CASE
		
		WHEN ( tclosed IS NOT NULL AND tcompleted IS NOT NULL AND tigx IS NOT NULL ) THEN
		ROUND( AVG( DATEDIFF( tclosed, tdate )), 1 ) 
		WHEN ( tclosed IS NULL AND tcompleted IS NULL AND tigx IS NULL ) THEN
		ROUND( AVG( DATEDIFF( CURDATE(), tdate )), 1 ) ELSE ROUND(
			AVG(
			DATEDIFF( COALESCE ( tclosed, tcompleted, tigx ), tdate )),
			1 
		END AS `avg` 
	FROM
		`tbl_c` 
	WHERE
		NOT ( `tkt` LIKE '%I%' OR `tkt` LIKE '%L%' AND tstate IN ( 'Closed' ) AND tigx IS NULL AND tclosed IS NULL AND tcompleted IS NULL ) 
	GROUP BY
		tarea 
ORDER BY
	tdate DESC;
9

K. Soā€¦ tell us where the parentheses should go.

Do you meanā€¦

`tkt` LIKE '%I%' OR (`tkt` LIKE '%L%' AND tstate IN ( 'Closed' ) AND tigx IS NULL AND tclosed IS NULL AND tcompleted IS NULL)

or

(`tkt` LIKE '%I%' OR `tkt` LIKE '%L%') AND tstate IN ( 'Closed' ) AND tigx IS NULL AND tclosed IS NULL AND tcompleted IS NULL

or some combination/modification thereof?

1 Like
10

I have tried with

``tkt LIKE '%I%' OR (tkt LIKE '%L%' AND tstate IN ( 'Closed' ) AND tigx IS NULL AND tclosed IS NULL AND tcompleted IS NULL)

and not working

so I tried that and it worked

WHERE
		NOT ( `tkt` LIKE '%I%' OR `tkt` LIKE '%L%' AND tstate IN ( 'Closed' ) AND tigx IS NULL AND tclosed IS NULL AND tcompleted IS NULL )
11

The important thing here is that operators donā€™t have the same precedence.

Like * and + donā€™t have the same precedence, AND and OR donā€™t either.

X OR Y AND Z
is read by the engine as X OR (Y AND Z), because AND has a higher precedence than OR.

Now consider how that interacts with the NOT as well.
What your WHERE condition reads as:

Find the rows where tkt is LIKE %L% AND tstate IN ( ā€˜Closedā€™ ) AND tigx IS NULL AND tclosed IS NULL AND tcompleted IS NULL
Find the rows where tkt is LIKE %I%.
Union those together.

Then give me every row that is not in that union.

1 Like
12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.