CODEWITHSUNDEEP
mysqlsql
Eilidh
Eilidh

Reputation: 1298

How can I use a subquery on my query results and then ORDER BY a calculated result for each row?

Context

I'm having some trouble putting together the logic of subqueries(?) together in my head. 

   *---------*---------*---------*------------*------------*---------*
   |GUEST_ID | Country | County  | Attending  | Donation   | Party   |
   *---------*---------*---------*------------*------------*---------*

I have a database containing records about attendees of an charity ball.

GUEST_ID: Table key.
Country: Country the guest is from.
County: County the guest is from (i.e. a region within that country).
Attending: Whether or not the guest is attending (i.e. true or false).
Donation: Amount the guest is donating to the cause.
Party: Which Party the guest is attending.


Goal

I wish to display a table broken down by Country and County, showing the number of attendees from each Country + County, and the average donation of those who are attending from that Country + Country. I'd then like to order the rows from highest average donation to lowest. I understand the constituent parts of this query, however I'm not sure how to 'glue' it together as a whole.

I can GROUP BY Country, County.

I can SUM(Donation).

I can COUNT(*) WHERE ATTENDING = 'Yes'

And I know I can SET @variables to store results in the interim.

I also know I can ORDER BY DESC.

So far

My issue is with understanding how to combine these elements into a functioning query. I'm guessing I need to use subqueries however it's getting the order right I'm having trouble with. This is what I have so far - 

SELECT SUM(`Donation`) AS `TotalDonations`, `Country`, `County`  
FROM `GuestList` 
WHERE `Party` = `2014CharityBall`
GROUP BY `Country`, `County`

I'm not sure how to add the subquery to find the COUNT of only those guests who are definitely attending, or how to calculate the TotalDonations / DefinitelyAttending and then ORDER BY this.


Results Required

*------------*------------*---------------*---------------*---------------*
| Country    | County     | # of Attendees|Total Donations|Avg. Donation  |
*------------*------------*---------------*---------------*---------------*

Country: Country the guests are from.
County: County the guests are from (i.e. a region within that country).
# of Attendees: Number of attendees (Attending = 'true') within that country and county.
Total Donations: Total donations of all those attending (Attending = 'true') within that country and county (e.g. SUM(Donation)).
Avg. Donation: Average donation of all those attending (Attending = 'true') within that country and county (e.g. AVG(Donation) - that is, ofc, Total Donations / # of Attendees).


Extra Credit

Just an expression ;)

If I want to calculate the Total Donations among all donators invited to the party, and, separately, the Total Donations solely among those donators attending the party, how would I do that?

Upvotes: 0

Views: 54

Answers (1)

JBrooks
JBrooks

Reputation: 10013

    SELECT `Country`, 
    `County`,
    SUM(`Donation`) AS `TotalDonations`,
    FORMAT(AVG(`Donation`), 0) AS `AVGDonations`,
    COUNT(1) Attending
    FROM `GuestList` 
    WHERE `Party` = `2014CharityBall`
           AND Attending = `Yes`
    GROUP BY `Country`, `County`
    Order by 4 desc

Upvotes: 2

Related Questions