Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Result without a subquery

I am trying to come up with a solution without a subquery which was asked in one of the interview.

customerId in Orders table is a foreign key referencing id in Customer table.

Table: Customer: id is a primary key
enter image description here
Table Orders
enter image description here

I was asked to calculate a percentage of each customer orders as compared to total units across the orders table. So, output should look like below:
Output:
enter image description here
Below was my solution:

select 
    c.name,
    COALESCE(SUM(o.units),0)/(select SUM(units) from orders)*100 as units
FROM customer c LEFT OUTER JOIN orders o ON c.id = o.customerid
    group by c.id

Then interviewer asked to come up with a single query solution without using a subquery.

My answer was since we need a grouped sum based on customer id and then total sum of units, we will need two queries.

Does anyone know how this can be solved without using a subquery?

Note:
I see couple of comments saying it will error out/ query is not valid, however, it runs fine:
enter image description here

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    select 
    DISTINCT c.name,
  coalesce(SUM(o.units) OVER(PARTITION BY c.id),0)/SUM(o.units) OVER() * 100 as percent
FROM customers c LEFT OUTER JOIN orders o ON c.id = o.customerid

  2. 0

    You can achieve what you want by using a windowed aggregate function to replace the subselect.

    Typically, the overall total would be calculated with something like SUM(o.units) OVER(). However, since we are already within the scope of a group-by, we need to reference the group-by sum within the window function sum – SUM(SUM(o.units)) OVER().

    The OVER() qualifier without a PARTITION BY or ORDER BY sets the window scope to be the entire result set (the grand total).

    SELECT
    c.id,
    c.name,
    SUM(o.units) As CustomerUnits,
    SUM(SUM(o.units)) OVER() AS TotalUnits,
    COALESCE(
        100.0 * SUM(o.units) / SUM(SUM(o.units)) OVER(),
        0
        ) As Percent
FROM customer c
LEFT JOIN orders o ON c.id = o.customerid
GROUP BY c.id;

    Results:

    id name CustomerUnits TotalUnits Percent
    1 Henry 1 10 10.00000
    2 Sally 0 10 0.00000
    3 Joe 9 10 90.00000
    4 Max null 10 0.00000

    See this db<>fiddle for a demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search