CODEWITHSUNDEEP
sql
Iron_Tri
Iron_Tri

Reputation: 85

SQL combine separate queries

Sorry for the beginner question. But how can I put these queries into one data set rather than multiple queries? They are all being taken out of the same table. Also as you can see there is an "Open_Time" This is a DATE format. How can I write that to say AND open_date is within the last 60 days?

SELECT COUNT(P_NUMBER)
FROM PROBLEM_REPORT
WHERE Assignment='Crosby'
AND Severity=4
AND Open_Time<=60;

SELECT COUNT(P_NUMBER)
FROM PROBLEM_REPORT
WHERE Assignment='Crosby'
AND Severity=5
AND Open_Time<=60;

SELECT COUNT(P_NUMBER)
FROM PROBLEM_REPORT
WHERE Assignment='Crosby'
AND Severity=4
AND Close_Time<=60;

SELECT COUNT(P_NUMBER)
FROM PROBLEM_REPORT
WHERE Assignment='Crosby'
AND Severity=4
AND Close_Time<=60;

SELECT COUNT(P_NUMBER)
FROM PROBLEM_REPORT
WHERE Assignment='EUC'
AND Severity=4
AND Open_Time<=60;

SELECT COUNT(P_NUMBER)
FROM PROBLEM_REPORT
WHERE Assignment='EUC'
AND Severity=5
AND Open_Time<=60;

SELECT COUNT(P_NUMBER)
FROM PROBLEM_REPORT
WHERE Assignment='EUC'
AND Severity=4
AND Close_Time<=60;

SELECT COUNT(P_NUMBER)
FROM PROBLEM_REPORT
WHERE Assignment='EUC'
AND Severity=4
AND Close_Time<=60;

Upvotes: 0

Views: 112

Answers (3)

Ankur Trapasiya
Ankur Trapasiya

Reputation: 2200

Try this sql.

SELECT COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 4 AND DATEDIFF(CURDATE(),Open_Time)<=60 THEN P_NUMBER END) as p1,
    COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 5 AND DATEDIFF(CURDATE(),Open_Time)<=60  THEN P_NUMBER END) as p2,
    COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 4 AND DATEDIFF(CURDATE(),Close_Time)<=60 THEN P_NUMBER END) as p3,
    COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 5 AND DATEDIFF(CURDATE(),Close_Time)<=60 THEN P_NUMBER END) as p4,
    COUNT(CASE WHEN Assignment = 'EUC' AND Severity = 4 AND DATEDIFF(CURDATE(),Open_Time)<=60 THEN P_NUMBER END) as p5,
    COUNT(CASE WHEN Assignment = 'EUC' AND Severity = 5 AND DATEDIFF(CURDATE(),Open_Time)<=60 THEN P_NUMBER END) as p6,
    COUNT(CASE WHEN Assignment = 'EUC' AND Severity = 4 AND DATEDIFF(CURDATE(),Close_Time)<=60 THEN P_NUMBER END) as p7,
    COUNT(CASE WHEN Assignment = 'EUC' AND Severity = 5 AND DATEDIFF(CURDATE(),Close_Time)<=60 THEN P_NUMBER END) as p8
FROM PROBLEM_REPORT
WHERE Assignment IN('EUC','Crosby')
AND Severity IN(4,5)

Upvotes: 1

John Woo
John Woo

Reputation: 263883

There WHERE clause in the query will make the query much faster since it will only calculate on the filtered rows.

SELECT  COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 4 AND Open_Time <= 60 THEN P_NUMBER END),
        COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 5 AND Open_Time <= 60 THEN P_NUMBER END),
        COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 4 AND Close_Time <= 60 THEN P_NUMBER END),
        COUNT(CASE WHEN Assignment = 'Crosby' AND Severity = 5 AND Close_Time <= 60 THEN P_NUMBER END),
        COUNT(CASE WHEN Assignment = 'EUC' AND Severity = 4 AND Open_Time <= 60 THEN P_NUMBER END),
        COUNT(CASE WHEN Assignment = 'EUC' AND Severity = 5 AND Open_Time <= 60 THEN P_NUMBER END),
        COUNT(CASE WHEN Assignment = 'EUC' AND Severity = 4 AND Close_Time <= 60 THEN P_NUMBER END),
        COUNT(CASE WHEN Assignment = 'EUC' AND Severity = 5 AND Close_Time <= 60 THEN P_NUMBER END)
FROM    PROBLEM_REPORT
WHERE   Assignment IN ('Crosby', 'EUC') AND
        Severity IN (4,5) AND
        (Open_Time <= 60 OR Close_Time <= 60)

Upvotes: 0

Dibstar
Dibstar

Reputation: 2364

One way of doing this:

SELECT 
SUM(CASE WHEN Assignment = 'Crosby' AND Severity=4 AND Open_Time<=60 THEN 1 ELSE 0 END) AS P_number1
,SUM(CASE WHEN Assignment = 'Crosby' AND Severity=5 AND Open_Time<=60 THEN 1 ELSE 0 END) AS P_number2
,SUM(CASE WHEN Assignment = 'Crosby' AND Severity=4 AND close_time<=60 THEN 1 ELSE 0 END) AS P_number3
,SUM(CASE WHEN Assignment = 'Crosby' AND Severity=5 AND close_time<=60 THEN 1 ELSE 0 END) AS P_number4
,SUM(CASE WHEN Assignment = 'EUC' AND Severity=4 AND Open_Time<=60 THEN 1 ELSE 0 END) AS P_number5
,SUM(CASE WHEN Assignment = 'EUC' AND Severity=5 AND Open_Time<=60 THEN 1 ELSE 0 END) AS P_number6
,SUM(CASE WHEN Assignment = 'EUC' AND Severity=4 AND close_time<=60 THEN 1 ELSE 0 END) AS P_number7
,SUM(CASE WHEN Assignment = 'EUC' AND Severity=5 AND close_time<=60 THEN 1 ELSE 0 END) AS P_number8
FROM PROBLEM_REPORT

Upvotes: 1

Related Questions