CODEWITHSUNDEEP
sqlsql-server
My2ndLovE
My2ndLovE

Reputation: 397

SQL Query: Retrieve columns with different condition

I need to retrieve multiple columns with different condition from the different table. What i have as below

SELECT
(SELECT COUNT(*) FROM Table1 WHERE Price >= 0 Price < 10) AS 'Group1',
(SELECT COUNT(*) FROM Table1 WHERE Price >= 10 AND Price < 20) AS 'Group2',
(SELECT COUNT(*) FROM Table1 WHERE Price >= 20 AND Price < 30) AS 'Group3',
(SELECT COUNT(*) FROM Table2 WHERE Price >= 0 Price < 10) AS 'Group4',
(SELECT COUNT(*) FROM Table2 WHERE Price >= 10 AND Price < 20) AS 'Group5',
(SELECT COUNT(*) FROM Table2 WHERE Price >= 20 AND Price < 30) AS 'Group6'

Just wonder is there any better way to restructure a better query for above condition?

Upvotes: 1

Views: 914

Answers (2)

user2135380
user2135380

Reputation: 31

Adding up table1 to the above wonderful query by J W.

SELECT  SUM(CASE WHEN T1.Price >= 0 Price < 10 THEN 1 END) AS 'Group1',
        SUM(CASE WHEN T1.Price >= 10 AND Price < 20 THEN 1 END) AS 'Group2',
        SUM(CASE WHEN T1.Price >= 20 AND Price < 30 THEN 1 END) AS 'Group3',
        SUM(CASE WHEN T2.Price >= 0 Price < 10 THEN 1 END) AS 'Group4',
        SUM(CASE WHEN T2.Price >= 10 AND Price < 20 THEN 1 END) AS 'Group5',
        SUM(CASE WHEN T2.Price >= 20 AND Price < 30 THEN 1 END) AS 'Group6'
FROM    table1 T1, table2 T2

Upvotes: 2

John Woo
John Woo

Reputation: 263683

There is,

SELECT  SUM(CASE WHEN Price >= 0 Price < 10 THEN 1 END) AS 'Group1',
        SUM(CASE WHEN Price >= 10 AND Price < 20 THEN 1 END) AS 'Group2',
        SUM(CASE WHEN Price >= 20 AND Price < 30 THEN 1 END) AS 'Group3',
        SUM(CASE WHEN Price >= 0 Price < 10 THEN 1 END) AS 'Group4',
        SUM(CASE WHEN Price >= 10 AND Price < 20 THEN 1 END) AS 'Group5',
        SUM(CASE WHEN Price >= 20 AND Price < 30 THEN 1 END) AS 'Group6'
FROM    table2

Upvotes: 4

Related Questions