CODEWITHSUNDEEP
sqlsql-server
AlmondMan
AlmondMan

Reputation: 17

Multiple Subqueries on the Same Column

I'm trying to pull back multiple different sums from one table using different criteria. The only problem is that my subqueries were pulling back more than one response, so I changed my query a bit and then got extremely lost trying to find the best way to do this.

Here's the query I'm working with right now:

   SELECT TER_ID AS TER_ID,
   SUM(MED_AMT) AS NET_SLS_AMT,
   SUM(SELECT MED_AMT
       FROM DAY_TER_MEDIA
       WHERE TND_CD = 1 AND
       DAY_TER_MEDIA.DT = '') AS CASH_AMT,
   SUM(SELECT MED_AMT
       FROM DAY_TER_MEDIA
       WHERE DAY_TER_MEDIA.TND_CD IN(4,5,16,18,23,31) AND
       DAY_TER_MEDIA.DT = '') AS ELEC_AMT,
   SUM(SELECT MED_AMT
       FROM DAY_TER_MEDIA
       WHERE DAY_TER_MEDIA.TND_CD IN(2,3,8,9,10,11,15,20,52) AND
       DAY_TER_MEDIA.DT = '') AS OTHER_AMT
   FROM DAY_TER_MEDIA
   WHERE DT = ''
   GROUP BY TER_ID;

As you can see, my intention is to pull back NET_SLS_AMT, CASH_AMT, ELEC_AMT, and OTHER_AMT all from the same column and sum only those with a certain TND_CD, DT, and TER_ID. As I said, I've been changing this query up multiple times and I know the current iteration of it does not actually do what I need it to. I'm a bit lost as to how to do this while still grouping them all by date and TER_ID. Any ideas? Or is there even a clean way to do this?

Upvotes: 0

Views: 309

Answers (3)

David Rushton
David Rushton

Reputation: 5030

One approach is to use a CASE expression to return conditional totals. My example uses the following sample data:

Sample Data

/* Using table variables makes sharing sample data easy.
 */
DECLARE @Example TABLE
    (
        [Type]  INT,
        AMT     MONEY
    )
;

INSERT INTO @Example
    (
        [Type],
        AMT
    )
VALUES
    (1, 10),
    (1, 15),
    (1, 45),
    (2, 30),
    (2, 20)
;

In the example each column conditionally contains the AMT or 0, based on the Type. The SUM function aggregates the results.

Example

/* Removing the SUM functions would highlight how this technique works.
 */
SELECT
    SUM(CASE WHEN [Type] = 1 THEN AMT ELSE 0 END) AS AMT_1,
    SUM(CASE WHEN [Type] = 2 THEN AMT ELSE 0 END) AS AMT_2
FROM
    @Example
;

Upvotes: 0

JNevill
JNevill

Reputation: 50019

The reason you are not getting your expected results, is because there is no relationship between your subqueries and the table in your FROM. You would need to add that relationship in each of your subqueries WHERE clauses (which is called a Correlated Subquery since it uses values from the outer query in the subquery):

SELECT TER_ID AS TER_ID,
SUM(MED_AMT) AS NET_SLS_AMT,
SUM(SELECT MED_AMT
   FROM DAY_TER_MEDIA
   WHERE TND_CD = 1 AND TER_ID = t1.TER_ID AND
   DAY_TER_MEDIA.DT = '') AS CASH_AMT,
SUM(SELECT MED_AMT
   FROM DAY_TER_MEDIA
   WHERE DAY_TER_MEDIA.TND_CD IN(4,5,16,18,23,31) AND TER_ID = t1.TER_ID AND
   DAY_TER_MEDIA.DT = '') AS ELEC_AMT,
SUM(SELECT MED_AMT
   FROM DAY_TER_MEDIA
   WHERE DAY_TER_MEDIA.TND_CD IN(2,3,8,9,10,11,15,20,52) AND TER_ID = t1.TER_ID AND
   DAY_TER_MEDIA.DT = '') AS OTHER_AMT
FROM DAY_TER_MEDIA t1
WHERE DT = ''
GROUP BY TER_ID;

But this is a very inefficient way of doing this. Instead, use CASE statements without any subqueries:

SELECT 
    TER_ID,
    SUM(CASE WHEN TND_CD = 1 THEN MED_AMT ELSE 0 END) as CASH_AMT,
    SUM(CASE WHEN TND_CD IN(4,5,16,18,23,31) THEN MED_AMT ELSE 0 END) as ELEC_AMT,
    SUM(CASE WHEN TND_CD IN(2,3,8,9,10,11,15,20,52) THEN MED_AMT ELSE 0 END) AS OTHER_AMT
FROM DAY_TER_MEDIA
WHERE DT= ''
GROUP BY TER_ID

Upvotes: 2

JamieD77
JamieD77

Reputation: 13949

Use case statements instead of selects

SELECT TER_ID AS TER_ID,
        SUM(MED_AMT) AS NET_SLS_AMT,
        SUM(CASE WHEN TND_CD = 1 THEN MED_AMT
                 ELSE 0
            END) AS CASH_AMT,
        SUM(CASE WHEN DAY_TER_MEDIA.TND_CD IN (4,5,16,18,23,31) THEN MED_AMT
                 ELSE 0
            END) AS ELEC_AMT,
        SUM(CASE WHEN DAY_TER_MEDIA.TND_CD IN (2,3,8,9,10,11,15,20,52) THEN MED_AMT
                 ELSE 0
            END) AS OTHER_AMT
 FROM   DAY_TER_MEDIA
 WHERE  DT = ''
 GROUP BY TER_ID;

Upvotes: 1

Related Questions