How to combine multiple SQL aggregate queries into a single query

Question

I have a number of queries that I would like to combine into a single query, even though they have a common filtering field, I am not completely sure that what I am trying to achieve is even possible since these are all aggregate queries.

declare @salesforceId int
set @salesforceId = 109924

SELECT 
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating, 
COUNT(*) as ReviewCount FROM QuestionResponse qr
                JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
                AND sr.StatusId IN (5, 7)
                AND qr.QuestionId = 1 --general feedback
                AND sr.RestaurantNetworkId = @salesforceId

SELECT 
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating, 
COUNT(*) as ReviewCount FROM QuestionResponse qr
                JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
                AND sr.StatusId IN (5, 7)
                AND qr.QuestionId = 3 --food rating
                AND sr.RestaurantNetworkId = @salesforceId

SELECT 
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating, 
COUNT(*) as ReviewCount FROM QuestionResponse qr
                JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
                AND sr.StatusId IN (5, 7)
                AND qr.QuestionId = 4 --drinks rating
                AND sr.RestaurantNetworkId = @salesforceId

SELECT 
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating, 
COUNT(*) as ReviewCount FROM QuestionResponse qr
                JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
                AND sr.StatusId IN (5, 7)
                AND qr.QuestionId = 5 -- restaurant ambience
                AND sr.RestaurantNetworkId = @salesforceId

SELECT 
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating, 
COUNT(*) as ReviewCount FROM QuestionResponse qr
                JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
                AND sr.StatusId IN (5, 7)
                AND qr.QuestionId = 6 -- service rating
                AND sr.RestaurantNetworkId = @salesforceId

SELECT 
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating, 
COUNT(*) as ReviewCount FROM QuestionResponse qr
                JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
                AND sr.StatusId IN (5, 7)
                AND qr.QuestionId = 7 -- booking service
                AND sr.RestaurantNetworkId = @salesforceId

SELECT 
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating, 
COUNT(*) as ReviewCount FROM QuestionResponse qr
                JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
                AND sr.StatusId IN (5, 7)
                AND qr.QuestionId = 12 -- Recommend Restaurant
                AND sr.RestaurantNetworkId = @salesforceId

SELECT 
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating, 
COUNT(*) as ReviewCount FROM QuestionResponse qr
                JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
                AND sr.StatusId IN (5, 7)
                AND qr.QuestionId = 13 -- Overall Rating
                AND sr.RestaurantNetworkId = @salesforceId

SELECT 
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating, 
COUNT(*) as ReviewCount FROM QuestionResponse qr
                JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
                AND sr.StatusId IN (5, 7)
                AND qr.QuestionId = 525 -- Value for Money
                AND sr.RestaurantNetworkId = @salesforceId

SELECT 
AVG(CAST(qr.RatingScaleOptionId as float)) as Rating, 
COUNT(*) as ReviewCount FROM QuestionResponse qr
                JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
                AND sr.StatusId IN (5, 7)
                AND qr.QuestionId = 526 -- Location
                AND sr.RestaurantNetworkId = @salesforceId

SELECT 
count(*) as Total,
    CultureInvariantText AS Tag
    FROM
        SurveyResponse SR 
    INNER JOIN 
        [QuestionResponseFixedOptions] QR ON SR.SurveyResponseId = QR.SurveyResponseId
    INNER JOIN
        QuestionResponseOption QRO ON QR.[ResponseOptionId] = QRO.[ResponseOptionId]
    INNER JOIN
        Question Q ON QRO.QuestionId = Q.QuestionId
    INNER JOIN
        LocalizableText LT ON QRO.ResponseValue = LT.LocalizableTextId  
    where sr.RestaurantNetworkId = @salesforceId
    group by CultureInvariantText

output can be something along the lines of the following: count is the same across all queries.

|GeneralFeedback|FoodRating|....|.....|....|Count|

How your final output should look like? it seems like all queries have same columns you can use UNION All — Tassadaque
– Tassadaque, Commented Jan 26, 2018 at 11:47
Keep the common conditions in the WHERE clause. Use case expressions in the select list to do conditional aggregation. — jarlh
– jarlh, Commented Jan 26, 2018 at 11:48

Markus Winand · Accepted Answer · 2018-01-26 11:57:46Z

1

If they are all accessing the same table, you should do conditional aggregates:

AVG(CAST CASE WHEN <condtion> THEN qr.RatingScaleOptionId END as float)

You can have as many of those in your SELECT clause you like. Just make sure to only keep the common conditions in the WHERE clause. The varying conditions then belong into the WHEN of the CASE.

Read more about this here: https://modern-sql.com/feature/filter

answered Jan 26, 2018 at 11:57

Markus Winand

8,8641 gold badge38 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sergey Kalinichenko · Accepted Answer · 2018-01-26 11:50:53Z

You can add a surrogate column for telling which aggregate a row represents, format each query to produce identical rows, and use UNION ALL to combine them:

SELECT 'GeneralFeedback' AS Type, AVG(...) AS Rating, COUNT(*) AS ReviewCount
FROM QuestionResponse ...
UNION ALL
SELECT 'FoodRating' AS Type, AVG(...) AS Rating, COUNT(*) AS ReviewCount
FROM QuestionResponse ...
...
UNION ALL
SELECT 'Total' as Type, 0 as Rating, COUNT(*) as ReviewCount
FROM SurveyResponse ...

The row for the 'Total' is "shoehorned" into the common structure by adding a column with zero rating and calling Total a ReviewCount. The code that executes this query will need special processing to treat this row separately from other rows coming back from the query.

OscarSosa · Accepted Answer · 2018-01-26 12:09:08Z

Try this. Please note that I changed the float for decimal. You will have better precision, depending on which kind of application you are working for. You need to finalize the query according to your ~10 cases.

SELECT AVG(  CASE WHEN qr.QuestionId = 1 THEN CAST(qr.RatingScaleOptionId as decimal) END as GeneralFeedback_Rating
     , COUNT(CASE WHEN qr.QuestionId = 1 THEN 1 END)                                      as GeneralFeedback_Count
     , AVG(  CASE WHEN qr.QuestionId = 3 THEN CAST(qr.RatingScaleOptionId as decimal) END as Food_Rating
     , COUNT(CASE WHEN qr.QuestionId = 3 THEN 1 END)                                      as Food_Count
     , AVG(  CASE WHEN qr.QuestionId = 4 THEN CAST(qr.RatingScaleOptionId as decimal) END as Drinks_Rating
     , COUNT(CASE WHEN qr.QuestionId = 4 THEN 1 END)                                      as Drinks_Count
-- ... ... ...
  FROM QuestionResponse qr
  JOIN SurveyResponse sr
    ON qr.SurveyResponseId    = sr.SurveyResponseId
   AND sr.StatusId           IN (5, 7)
   AND sr.RestaurantNetworkId = @salesforceId
   AND qr.QuestionId         IN (1, 3, 4)--1:general feedback, 3:food, 4:drinks, ETC...

paparazzo · Accepted Answer · 2018-01-26 12:14:14Z

0

try group by

declare @salesforceId int
set @salesforceId = 109924

SELECT qr.QuestionId, 
       AVG(CAST(qr.RatingScaleOptionId as float)) as Rating, 
       COUNT(*) as ReviewCount 
FROM QuestionResponse qr
JOIN SurveyResponse sr ON qr.SurveyResponseId = sr.SurveyResponseId
 AND sr.StatusId IN (5, 7)
 AND sr.RestaurantNetworkId = @salesforceId 
group by qr.QuestionId

answered Jan 26, 2018 at 12:14

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Collectives™ on Stack Overflow

How to combine multiple SQL aggregate queries into a single query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related