CODEWITHSUNDEEP
mysql
XenPanda
XenPanda

Reputation: 137

Mysql...Impossible query?

Difficult query. Not sure if this is possible.

I am trying to figure out how to format a query that needs to accomplish several things.

(1) I need to parse the DATE field that is a VARCHAR field and not a sql date field to get the month of each date.

(2) I then need to AVG all the PTS fields by NAME and Month. So with my example data below I would have a row that has John and John would have 2 in the JAN column and 3 in the the APR column and the AVG column would be an average of all the months. So the months are an average of all the entries in that month and the AVG column is an average of all the columns in the row.

Table:

Name (VARCAHR)  PTS (INT) DATE (VARCHAR)        
---------------------------------------------
John            3           Tue Apr 14 17:56:02 2020
Chris           2           Tue Apr 14 19:44:03 2020
John            2           Mon Jan 30 15:23:03 2020
Chris           4           Fri Feb 28 16:15:15 2020
John            3           Tue Apr 14 17:56:02 2020


Table Layout on web page:
Name        Jan     Feb     Mar     Apr     May     Jun     Jul     Aug     Sep     Oct     Nov     Dec     Average

Upvotes: 0

Views: 103

Answers (1)

Nick
Nick

Reputation: 147206

Not impossible, just convoluted. You can use STR_TO_DATE to convert your strings into DATETIME objects, from which you can then use MONTH to get the month number. Note though (as @DRapp commented) that you should be storing DATETIME values in their native form, not as VARCHAR, then you wouldn't have to deal with STR_TO_DATE. Having got the month number, you can then use conditional aggregation to get the results you want:

SELECT name,
       COALESCE(AVG(CASE WHEN mth = 1 THEN PTS END), 0) AS Jan,
       COALESCE(AVG(CASE WHEN mth = 2 THEN PTS END), 0) AS Feb,
       COALESCE(AVG(CASE WHEN mth = 3 THEN PTS END), 0) AS Mar,
       COALESCE(AVG(CASE WHEN mth = 4 THEN PTS END), 0) AS Apr,
       -- repeat for May to November
       COALESCE(AVG(CASE WHEN mth = 12 THEN PTS END), 0) AS `Dec`,
       AVG(PTS) AS AVG
FROM (
  SELECT name, PTS AS PTS, MONTH(STR_TO_DATE(DATE, '%a %b %e %H:%i:%s %Y')) AS mth
  FROM data
) d
GROUP BY name

Output (for your sample data):

name    Jan     Feb     Mar     Apr     Dec     AVG
Chris   0       4       0       2       0       3
John    0       0       0       2.6667  0       2.6667

Demo on SQLFiddle

Upvotes: 3

Related Questions