CODEWITHSUNDEEP
mysqlsql
User12345
User12345

Reputation: 325

MySQL SELECT AS

Is there a way out to achieve something as below?

The below query is only to convey the intention. I do understand that it is syntactically incorrect.

SELECT 
        T_DATE,
        T_NAME,
        (SELECT MAX(DUR) WHERE STATUS = 'Y') AS UPD_MAX,
        (SELECT AVG(DUR) WHERE STATUS = 'Y') AS UPD_AVG,
        (SELECT MAX(DUR) WHERE STATUS = 'N') AS READ_MAX,
        (SELECT AVG(DUR) WHERE STATUS = 'N') AS READ_AVG
FROM
         TABLE_1
WHERE
         T_NAME LIKE 'ab%cd%'
GROUP BY 
         T_DATE,
         T_NAME

Upvotes: 0

Views: 73

Answers (2)

Coding Duchess
Coding Duchess

Reputation: 6919

try something like that:

SELECT 
    T_DATE,
    T_NAME,
    a.UPD_MAX,
    c.UPD_AVG,
    b.READ_MAX,
    d.READ_AVG
FROM TABLE_1
INNER JOIN (SELECT T_NAME, MAX(DUR) AS UPD_MAX FROM TABLE_1 WHERE STATUS = 'Y' GROUP BY T_NAME) as a ON T_NAME=a.T_NAME
 INNER JOIN (SELECT T_NAME, MAX(DUR) AS READ_MAX FROM TABLE_1 WHERE STATUS = 'N' GROUP BY T_NAME) as b ON T_NAME=b.T_NAME
INNER JOIN (SELECT T_NAME, AVG(DUR) AS UPD_AVG FROM TABLE_1 WHERE STATUS = 'Y' GROUP BY T_NAME) as c ON T_NAME=c.T_NAME
INNER JOIN (SELECT T_NAME, AVG(DUR) AS READ_AVG FROM TABLE_1 WHERE STATUS = 'N' GROUP BY T_NAME) as d ON T_NAME=d.T_NAME
WHERE T_NAME LIKE 'ab%cd%'
GROUP BY 
     T_DATE,
     T_NAME

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use conditional aggregation:

SELECT T_DATE, T_NAME,
       MAX(CASE WHEN STATUS = 'Y' THEN DUR END)  AS UPD_MAX,
       AVG(CASE WHEN STATUS = 'Y' THEN DUR END)  AS UPD_AVG,
       MAX(CASE WHEN STATUS = 'N' THEN DUR END)  AS READ_MAX,
       AVG(CASE WHEN STATUS = 'N' THEN DUR END)  AS READ_AVG
FROM TABLE_1
WHERE T_NAME LIKE 'ab%cd%'
GROUP BY T_DATE, T_NAME;

Upvotes: 2

Related Questions