FAQ: Advanced Aggregates - Product Sum 2

This community-built FAQ covers the “Product Sum 2” exercise from the lesson “Advanced Aggregates”.

Paths and Courses
This exercise can be found in the following Codecademy content:

SQL: Analyzing Business Metrics

FAQs on the exercise Product Sum 2

There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply (reply) below.

If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

hello to everybody,
i need heipl. can someone tell me, why in the second values in select we don’t need to specify the name. How do we get this percentage grouped by name?

the select quere uses only (sum(amount_paid)/(select sum(amount_paid) from order_items)*100.

for me it semms like every answer sould be 100.

sorry i can’t get it

many thanks in advice

zoia

Zoia,
I am just getting to this exercise, but the way I understand it only the numerator is affected by the GROUP BY.
In that way the numerator is the sum by item (name) and the denominator is the grand total, which includes all the items.

3 Likes

In the main query we calculate the revenue per name. This is then divided by the subquery, which simply sums up the total revenue across the rows. This is then multiplied by 100.0 to get a percentage.

You’re essentially combining these two tables:

SELECT name, SUM(amount_paid)
FROM order_items
GROUP BY 1
ORDER BY 2 DESC;
--> revenue per food item


SELECT SUM(amount_paid)
FROM order_items;
--> total revenue across food items (178311.5)

By combining them in one query:

SELECT name, ROUND(SUM(amount_paid)/(SELECT sum(amount_paid) FROM order_items)*100, 2)
FROM order_items
GROUP BY 1
ORDER BY 2 DESC;

The first query SELECT name, SUM(amount_paid) becomes the numerator, and SELECT sum(amount_paid) FROM order_items becomes the denominator. Within the ROUND() function, this division is multiplied by 100 to get a percentage, and then limited to two decimals. Swapping out the subquery for the value it calculates, 178311.5, would return the same table.

In regards to your questions 'Every answer should be 100 ': this would be the case if the denominator has the same value, however using the subquery makes sure that the denominator is the total revenue. Using the full query SELECT name, ROUND(SUM(amount_paid)/SUM(amount_paid)*100, 2) would accomplish that, however it wouldn’t be very useful in this context.

-T

4 Likes