[thelist] [SQL] SELECT Question

[Luther, Ron]

Jacques Capesius asked:


>>I am building a rather cumbersome SQL Query that has two sub-queries
in it, 


Hi Jacques,


Can you share the revelant portions of the table structures and maybe a 
few lines of example data and the result you would like to see?


If you had a summary level sales table (one line per product) and a
summary 
level quota table (one line per product), then a simple:

Select S.product, Sales/Quota as 'Percent to Quota' from 
Sales S, Quota Q where
S.product = Q.product

Might be enough.


If you had detail level sales data (multiple lines per product) and a 
summary level quota table (one line per product), then something like:

Select S.product, Sum(Sales)/Quota as 'Percent to Quota' from 
Sales S, Quota Q where
S.product = Q.product

Might be enough.


If you need to 'group' over multiple levels of product and sales 
office, (quota), hierarchies ... then it could get kinda ugly.


HTH,

RonL.