CODEWITHSUNDEEP
sqljoindatatableunion
L.Ethan
L.Ethan

Reputation: 11

SQL- How to get value from a different table without joining to use on a equation?

I have a table with total user count of a company by their divisions, and I have another table with total spend on multiple different products, I want to identify the per capita spend value for each product by dividing each total spend on a product by the total use count from all the divisions.

Division User Count
A 10
B 20
C 20
D 50
Total 100

Product Table,

Product Total Spend
Apple 670
Orange 580
Grapes 640
Tomato 1050

End result should be ,

Product Total Spend
Apple 6.7
Orange 5.8
Grapes 6.4
Tomato 10.5

Since there is nothing common among these tables to join, I need a way to get the total of the column of User count and use it in an equation in the query. It has to be dynamic so that even if the user count changes it will reflect on the per capita spend.

I'm using Zoho Analytics to do my online queries.

Upvotes: 0

Views: 742

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You can just aggregate before joining:

select p.*, p.spend * 1.0 / d.user_count
from product p cross join
     (select sum(user_count) as user_count
      from divisions
     ) d;

Upvotes: 2

Related Questions