What is best way to merge 2 select statement?

Question

I have a sql server query statement like this:

WITH A
AS (
    SELECT (
            SUM(CASE 
                    WHEN (t1.price) > 0
                        THEN (t1.price)
                    ELSE 0
                    END)
            ) AS pr1
        ,(
            ABS(SUM(CASE 
                        WHEN (t1.price) < 0
                            THEN (t1.price)
                        ELSE 0
                        END))
            ) AS pr2
    FROM dbo.price_table AS t1
    )
    ,B
AS (
    SELECT (WHEN(pr1 - pr2) < 0 THEN ABS(pr1 - pr2) ELSE 0 END) AS res
    FROM A
    )
SELECT res
FROM B

in my query, i use 2 select statement to achieve "res" column, but i want achieve to "res" column in 1 select statement.

what is best way for merge 2 select statement to 1 select statement query?

this sql query is a piece of my total query . and this just a example. i want to know how can merge with subquery of Other Ways... — Mahdi Radi, Apr 01 '15 at 14:52
Technically it _is_ one query. It will get compiled the same way a subquery would. What benefit do you hope to get from reorganizing it? — D Stanley, Apr 01 '15 at 14:53
my sql query created automatically by my c# program. so i want have 1 select statement not 2. — Mahdi Radi, Apr 01 '15 at 14:57
gordon gave you an answer already. and its one query anyway. — A ツ, Apr 01 '15 at 14:57

score 3 · Answer 1 · answered Apr 01 '15 at 14:45

3

Your calculation seems way to complicated. You are taking the sum of the positive values. Then the sum of the negative values, using ABS() to make that value positive, and subtracting this result. Guess what? That is the same as taking the SUM() of all the values in the first place.

So, I think this statement is equivalent:

SELECT (CASE WHEN SUM(t1.price) < 0
             THEN ABS(SUM(t1.price))
             ELSE 0
        END)
FROM dbo.price_table t1;

answered Apr 01 '15 at 14:45

Gordon Linoff

1,242,037
58
646
786

my sql query is just a example. i want to know how can merge with subquery or Other way – Mahdi Radi Apr 01 '15 at 14:49
@MehdiRadi . . . Then you should ask *another* question with a more realistic example. This question already has answers, so changing the question is impolite because it would invalidate the answers. – Gordon Linoff Apr 01 '15 at 15:07

score 0 · Answer 2 · answered Apr 01 '15 at 15:04

Common Table Expressions (CTEs) are a way of writing reusable sub-queries. There's not one method that works with all CTEs (CTEs can be self-referencing which is not possible with subqueries_. In your example, the query

WITH A AS
(
   {A query}
)
,B AS
(
   SELECT ... res FROM A
)
SELECT res FROM B

could be rearranged to

SELECT res FROM
(
   SELECT {expression} res FROM 
   (
        {A query}
   )
)

which is just

SELECT {expression} res FROM 
(
     {A query}
)

score 0 · Answer 3 · answered Apr 01 '15 at 17:56

your solution is fine. Even thou you use multipple "Select" statements, they will compile in one query. So your query is equivalent to the query proposed by Gordon Linoff.

If you have more complicated logic you can create SINGLE query: take your last "Select ..." and substituse "pr1" and "pr2" with subqueries (or expressions). You may receive very complex statement with repeatable parts.

When you use "WITH" you split logical parts with good readability, while performance is not decreased. I always prefere "WITH" in complicate queries.

thanks, but how can create a single query for my query? – Mahdi Radi Apr 01 '15 at 18:22 — Mahdi Radi, Apr 01 '15 at 18:22

What is best way to merge 2 select statement?

3 Answers3