CODEWITHSUNDEEP
sqlsql-serversql-server-2012subqueryquery-optimization
Carel
Carel

Reputation: 2143

Use Sub query result in arithmetic calculation

I have a query that contains a sub-query. The sub-query returns a COUNT() value. I want to display this count and then use it in a basic arithmetic calculation (subtract it from another column returned by the query).

Here's the query:

SELECT DISTINCT
    scw.SUBCOUNCIL_ID AS 'Sub Council',
    eiw.WARD_ID AS 'Ward',
    ess.DESCRIPTION AS 'Sector',
    essg.NO_OF_SEATS AS 'Sector Seats',
    (SELECT
        COUNT(in_en.NOMINATION_ID)
     FROM
        ELEC_NOMINATIONS in_en
        INNER JOIN ELEC_INTEREST_WARD in_eiw ON in_en.INTEREST_WARD_ID = in_eiw.INTEREST_WARD_ID
        INNER JOIN ELEC_SPEAKER_SECTOR in_ess ON in_eiw.SPEAKER_SECTOR_ID = in_ess.SPEAKER_SECTOR_ID
        INNER JOIN WARD in_w ON in_eiw.WARD_ID = in_w.WARD_ID
        INNER JOIN SUBCOUNCILWARD in_scw ON in_w.WARD_ID = in_scw.WARD_ID
     WHERE
        in_en.ELECTED = 1
        AND in_scw.SUBCOUNCIL_ID = scw.SUBCOUNCIL_ID
        AND in_eiw.WARD_ID = eiw.WARD_ID
        AND in_ess.SPEAKER_SECTOR_ID = ess.SPEAKER_SECTOR_ID) AS 'Elected',
    (essg.NO_OF_SEATS - "USE SUB-QUERY VALUE HERE") AS 'Vacant'
FROM
    ELEC_NOMINATIONS en
    JOIN PERSON p ON p.PERSON_ID = en.PERSON_ID
    INNER JOIN ELEC_VACANCY ev ON en.VACANCY_ID = ev.VACANCY_ID
    INNER JOIN ELEC_INTEREST_WARD eiw ON en.INTEREST_WARD_ID = eiw.INTEREST_WARD_ID
    INNER JOIN WARD w ON eiw.WARD_ID = w.WARD_ID
    INNER JOIN SUBCOUNCILWARD scw ON w.WARD_ID = scw.WARD_ID
    INNER JOIN ELEC_SPEAKER_SECTOR_GROUP essg ON ev.VACANCY_ID = essg.VACANCY_ID
    INNER JOIN ELEC_SECTOR_GROUP_SECTORS esgs ON essg.SPEAKER_GROUP_SECTOR_ID = esgs.SPEAKER_GROUP_SECTOR_ID
    INNER JOIN ELEC_SPEAKER_SECTOR ess ON esgs.SPEAKER_SECTOR_ID = ess.SPEAKER_SECTOR_ID
WHERE
    scw.SUBCOUNCIL_ID = 15
GROUP BY
    scw.SUBCOUNCIL_ID,
    eiw.WARD_ID,
    ess.SPEAKER_SECTOR_ID,
    en.PERSON_ID,
    p.FULLNAMES,
    p.SURNAME,
    en.REJECTED,
    ev.SEAT_TYPE,
    ev.TOTAL_SEATS,
    essg.NO_OF_SEATS,
    eiw.INTEREST_WARD_ID,
    eiw.WARD_ID,
    ess.DESCRIPTION

I want to subtract the count (named 'Elected') from the 'Sector Seats' column and display it as 'Vacant'. It works perfectly if I put the whole sub-query into the place where I want to do the subtraction, but I don't want to do that.

EDIT: The solution I used by combining the 2 provided answers:

SELECT DISTINCT
    scw.SUBCOUNCIL_ID AS [Sub Council],
    eiw.WARD_ID AS [Ward],
    ess.DESCRIPTION AS [Sector],
    essg.NO_OF_SEATS AS [Sector Seats],
    ISNULL(elec.Elected, 0) AS [Elected],
    essg.NO_OF_SEATS - ISNULL(elec.Elected, 0) AS [Vacant]
FROM
    ELEC_NOMINATIONS en
    JOIN PERSON p ON p.PERSON_ID = en.PERSON_ID
    INNER JOIN ELEC_VACANCY ev ON en.VACANCY_ID = ev.VACANCY_ID
    INNER JOIN ELEC_INTEREST_WARD eiw ON en.INTEREST_WARD_ID = eiw.INTEREST_WARD_ID
    INNER JOIN WARD w ON eiw.WARD_ID = w.WARD_ID
    INNER JOIN SUBCOUNCILWARD scw ON w.WARD_ID = scw.WARD_ID
    INNER JOIN ELEC_SPEAKER_SECTOR_GROUP essg ON ev.VACANCY_ID = essg.VACANCY_ID
    INNER JOIN ELEC_SECTOR_GROUP_SECTORS esgs ON essg.SPEAKER_GROUP_SECTOR_ID = esgs.SPEAKER_GROUP_SECTOR_ID
    INNER JOIN ELEC_SPEAKER_SECTOR ess ON esgs.SPEAKER_SECTOR_ID = ess.SPEAKER_SECTOR_ID
    LEFT JOIN
    (SELECT
        in_scw.SUBCOUNCIL_ID, 
        in_eiw.WARD_ID, 
        in_ess.SPEAKER_SECTOR_ID ,
        COUNT(in_en.NOMINATION_ID) AS [Elected]
    FROM
        ELEC_NOMINATIONS in_en
        INNER JOIN ELEC_INTEREST_WARD in_eiw ON in_en.INTEREST_WARD_ID = in_eiw.INTEREST_WARD_ID
        INNER JOIN ELEC_SPEAKER_SECTOR in_ess ON in_eiw.SPEAKER_SECTOR_ID = in_ess.SPEAKER_SECTOR_ID
        INNER JOIN WARD in_w ON in_eiw.WARD_ID = in_w.WARD_ID
        INNER JOIN SUBCOUNCILWARD in_scw ON in_w.WARD_ID = in_scw.WARD_ID
    WHERE   in_en.ELECTED = 1
    GROUP BY
        in_scw.SUBCOUNCIL_ID,
        in_eiw.WARD_ID,
        in_ess.SPEAKER_SECTOR_ID) elec ON elec.SUBCOUNCIL_ID = scw.SUBCOUNCIL_ID
                                       AND elec.WARD_ID = eiw.WARD_ID
                                       AND elec.SPEAKER_SECTOR_ID = ess.SPEAKER_SECTOR_ID
    WHERE
        essg.NO_OF_SEATS > 0
        AND scw.SUBCOUNCIL_ID = 15
GROUP BY
    scw.SUBCOUNCIL_ID,
    eiw.WARD_ID,
    ess.SPEAKER_SECTOR_ID,
    essg.NO_OF_SEATS,
    ess.DESCRIPTION,
    elec.Elected

Upvotes: 0

Views: 495

Answers (2)

GarethD
GarethD

Reputation: 69759

You can move the correlated subquery to an APPLY then you can reference the result multiple times:

SELECT DISTINCT
    scw.SUBCOUNCIL_ID AS 'Sub Council',
    eiw.WARD_ID AS 'Ward',
    ess.DESCRIPTION AS 'Sector',
    essg.NO_OF_SEATS AS 'Sector Seats',
    e.Elected,
    (essg.NO_OF_SEATS - e.Elected) AS 'Vacant'
FROM
    ELEC_NOMINATIONS en
    JOIN PERSON p ON p.PERSON_ID = en.PERSON_ID
    INNER JOIN ELEC_VACANCY ev ON en.VACANCY_ID = ev.VACANCY_ID
    INNER JOIN ELEC_INTEREST_WARD eiw ON en.INTEREST_WARD_ID = eiw.INTEREST_WARD_ID
    INNER JOIN WARD w ON eiw.WARD_ID = w.WARD_ID
    INNER JOIN SUBCOUNCILWARD scw ON w.WARD_ID = scw.WARD_ID
    INNER JOIN ELEC_SPEAKER_SECTOR_GROUP essg ON ev.VACANCY_ID = essg.VACANCY_ID
    INNER JOIN ELEC_SECTOR_GROUP_SECTORS esgs ON essg.SPEAKER_GROUP_SECTOR_ID = esgs.SPEAKER_GROUP_SECTOR_ID
    INNER JOIN ELEC_SPEAKER_SECTOR ess ON esgs.SPEAKER_SECTOR_ID = ess.SPEAKER_SECTOR_ID
    OUTER APPLY
    (   SELECT COUNT(in_en.NOMINATION_ID) AS Elected
         FROM
            ELEC_NOMINATIONS in_en
            INNER JOIN ELEC_INTEREST_WARD in_eiw ON in_en.INTEREST_WARD_ID = in_eiw.INTEREST_WARD_ID
            INNER JOIN ELEC_SPEAKER_SECTOR in_ess ON in_eiw.SPEAKER_SECTOR_ID = in_ess.SPEAKER_SECTOR_ID
            INNER JOIN WARD in_w ON in_eiw.WARD_ID = in_w.WARD_ID
            INNER JOIN SUBCOUNCILWARD in_scw ON in_w.WARD_ID = in_scw.WARD_ID
         WHERE
            in_en.ELECTED = 1
            AND in_scw.SUBCOUNCIL_ID = scw.SUBCOUNCIL_ID
            AND in_eiw.WARD_ID = eiw.WARD_ID
            AND in_ess.SPEAKER_SECTOR_ID = ess.SPEAKER_SECTOR_ID
    )  AS e
WHERE
    scw.SUBCOUNCIL_ID = 15
GROUP BY
    scw.SUBCOUNCIL_ID,
    eiw.WARD_ID,
    ess.SPEAKER_SECTOR_ID,
    en.PERSON_ID,
    p.FULLNAMES,
    p.SURNAME,
    en.REJECTED,
    ev.SEAT_TYPE,
    ev.TOTAL_SEATS,
    essg.NO_OF_SEATS,
    eiw.INTEREST_WARD_ID,
    eiw.WARD_ID,
    ess.DESCRIPTION

It is also worth noting that using literals for column aliases is on the deprecation list. So instead of

ess.DESCRIPTION AS 'Sector',

You should probably be using:

ess.DESCRIPTION AS Sector,

Or

ess.DESCRIPTION AS [Sector]

Although My personal preference for reasons explained here is:

Sector = ess.DESCRIPTION,

EDIT

I am not sure you need a correlated subquery, or an APPLY at all, I think you can do this using a normal subquery and a JOIN:

LEFT JOIN
(   SELECT  in_scw.SUBCOUNCIL_ID, 
            in_eiw.WARD_ID, 
            in_ess.SPEAKER_SECTOR_ID ,
            COUNT(in_en.NOMINATION_ID) AS Elected
        FROM
        ELEC_NOMINATIONS in_en
        INNER JOIN ELEC_INTEREST_WARD in_eiw ON in_en.INTEREST_WARD_ID = in_eiw.INTEREST_WARD_ID
        INNER JOIN ELEC_SPEAKER_SECTOR in_ess ON in_eiw.SPEAKER_SECTOR_ID = in_ess.SPEAKER_SECTOR_ID
        INNER JOIN WARD in_w ON in_eiw.WARD_ID = in_w.WARD_ID
        INNER JOIN SUBCOUNCILWARD in_scw ON in_w.WARD_ID = in_scw.WARD_ID
        WHERE   in_en.ELECTED = 1
        GROUP BY in_scw.SUBCOUNCIL_ID, in_eiw.WARD_ID, in_ess.SPEAKER_SECTOR_ID 
) e
    ON  e.SUBCOUNCIL_ID = scw.SUBCOUNCIL_ID
    AND e.WARD_ID = eiw.WARD_ID
    AND e.SPEAKER_SECTOR_ID = ess.SPEAKER_SECTOR_ID

Then just reference e.Elected as in the above code with the APPLY

Upvotes: 3

Bill Gregg
Bill Gregg

Reputation: 7147

Do you really need this subquery at all? It looks like you are joining to all the same tables in your main query, and you are just trying to count the number of rows where "ELEC_NOMINATIONS.Elected = 1". If that's the case, I think this would work:

SELECT DISTINCT
    scw.SUBCOUNCIL_ID AS 'Sub Council',
    eiw.WARD_ID AS 'Ward',
    ess.DESCRIPTION AS 'Sector',
    essg.NO_OF_SEATS AS 'Sector Seats',
    SUM(case when en.Elected = 1 then 1 else 0 end) as 'Elected',
    (essg.NO_OF_SEATS - SUM(case when en.Elected = 1 then 1 else 0 end)) AS 'Vacant'
FROM
    ELEC_NOMINATIONS en
    JOIN PERSON p ON p.PERSON_ID = en.PERSON_ID
    INNER JOIN ELEC_VACANCY ev ON en.VACANCY_ID = ev.VACANCY_ID
    INNER JOIN ELEC_INTEREST_WARD eiw ON en.INTEREST_WARD_ID = eiw.INTEREST_WARD_ID
    INNER JOIN WARD w ON eiw.WARD_ID = w.WARD_ID
    INNER JOIN SUBCOUNCILWARD scw ON w.WARD_ID = scw.WARD_ID
    INNER JOIN ELEC_SPEAKER_SECTOR_GROUP essg ON ev.VACANCY_ID = essg.VACANCY_ID
    INNER JOIN ELEC_SECTOR_GROUP_SECTORS esgs ON essg.SPEAKER_GROUP_SECTOR_ID = esgs.SPEAKER_GROUP_SECTOR_ID
    INNER JOIN ELEC_SPEAKER_SECTOR ess ON esgs.SPEAKER_SECTOR_ID = ess.SPEAKER_SECTOR_ID
WHERE
    scw.SUBCOUNCIL_ID = 15
GROUP BY
    scw.SUBCOUNCIL_ID,
    eiw.WARD_ID,
    ess.SPEAKER_SECTOR_ID,
    en.PERSON_ID,
    p.FULLNAMES,
    p.SURNAME,
    en.REJECTED,
    ev.SEAT_TYPE,
    ev.TOTAL_SEATS,
    essg.NO_OF_SEATS,
    eiw.INTEREST_WARD_ID,
    eiw.WARD_ID,
    ess.DESCRIPTION

Upvotes: 1

Related Questions