question

Seunswift avatar image
Seunswift asked

Querying a many to many relationship Table

parch-and-posey.png

With this schema, I am trying to get the total revenue (SUM(total_amt_usd)) from the orders table but grouped by the channels in web_events table.

I have tried the query below but my result is wrong.


SELECT w.channel AS channels,

  SUM(o.total_amt_usd) AS total_revenue

FROM orders o

JOIN accounts a

ON o.account_id = a.id

JOIN web_events w

ON w.account_id = a.id

GROUP BY 1

ORDER BY 2 DESC


This returns output.png

I know this is wrong because my total revenue from the orders table is $23million.



Please how best can I do this ?

tablesjoininner join
parch-and-posey.png (192.7 KiB)
output.png (20.6 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

For that schema, given the one-to-many relationship between accounts and web_events, there is no way to assign an order to a specific web_events.channel, so you are duplicating data when you sum the revenue.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Exactly the issue I have, I downloaded the merged tables, and the entries were duplicated, the reason for the overshot revenue.


Thanks kev.


0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.