CODEWITHSUNDEEP
sqlsql-servert-sql
Farhad-Taran
Farhad-Taran

Reputation: 6540

How to combine multiple SQL aggregate queries into a single query

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|

Upvotes: 0

Views: 203

Answers (4)

paparazzo
paparazzo

Reputation: 45106

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

Upvotes: 0

OscarSosa
OscarSosa

Reputation: 89

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...

Upvotes: 0

Markus Winand
Markus Winand

Reputation: 8746

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

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727067

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.

Upvotes: 0

Related Questions