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.
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.
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.
*------------*------------*---------------*---------------*---------------*
| 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).
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?
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments