CODEWITHSUNDEEP
sqlsubquerypivot
Bharat Koshti
Bharat Koshti

Reputation: 81

How to write single SQL query to fetch multiple results from same column with different conditions

SELECT 
(SELECT mainop 
FROM data_final2 
WHERE (id = A.id) 
AND (os = 1 and test = 2 and mainop BETWEEN 0 AND 500)) AS op1, 

(SELECT mainop 
FROM data_final2 
WHERE (id = A.id) 
AND (os = 1 and test = 2 and mainop BETWEEN 500 AND 1000)) AS op2, 

(SELECT mainop 
FROM data_final2 
WHERE (id = A.id) 
AND (os = 1 and test = 2 and mainop BETWEEN 1001 AND 1500)) AS op3 

FROM data_final2 AS A WHERE A.mainop BETWEEN 0 AND 500 OR A.mainop BETWEEN 500 AND 1000 OR A.mainop BETWEEN 1001 AND 1500

I want to write this query in single SQL query without using sub-queries with each outputs % from total count.

Also want to remove Null from output

[{"name":"op1","data":[229,null,null,268,null,3

Upvotes: 0

Views: 110

Answers (1)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

You can use a CASE statement to display your data based on multiple conditions on the same column.

SELECT
    CASE 
        WHEN mainop BETWEEN 0 AND 500
            AND os = 1 AND test = 2
            THEN mainop
        ELSE NULL
    END as op1
    , CASE 
        WHEN mainop BETWEEN 501 AND 1000
            AND os = 1 AND test = 2
            THEN mainop
        ELSE NULL
    END as op2
   , CASE 
        WHEN mainop BETWEEN 1001 AND 1500
            AND os = 1 AND test = 2
            THEN mainop
        ELSE NULL
    END as op3
FROM data_final2
WHERE mainop BETWEEN 0 AND 1500

You can also specify multiple CASE statements on the same column to generate different data.

Update:

After re-reading your question, I think you could actually be looking for something like this:

SELECT 100 * count(CASE 
            WHEN mainop BETWEEN 0 AND 500
                AND os = 1 AND test = 2
                    THEN 1
                ELSE NULL
        END) * 1.0 / (SELECT count(*) FROM data_final2) AS 'op1'
    ,100 * count(CASE 
            WHEN mainop BETWEEN 501 AND 1000
                AND os = 1 AND test = 2
                    THEN 1
                ELSE NULL
        END) * 1.0 / (SELECT count(*) FROM data_final2) AS 'op2'
    ,100 * count(CASE WHEN mainop BETWEEN 1001 AND 1500
                AND os = 1 AND test = 2
                    THEN 1
                ELSE NULL
        END) * 1.0 / (SELECT count(*) FROM data_final2) AS 'op3'
FROM data_final2
WHERE mainop BETWEEN 0 AND 1500

If you're looking for displaying this row as a percentage number, please look at your specific RDBMS functions to convert the data to the appropriate type.

Also, here is a SQLFiddle with what the query returns.

Upvotes: 1

Related Questions