kmeek@cti1.UUCP (Kevin Meek) (11/20/90)
Here is a Question for all you SQL guru's out there. We have a table with account numbers and account information. We have a table with planning data that is linked to the account table via the account number. We have a table with spending data that is linked to the account table via the account number. The question: We want an output record for each planning record with the corresponding account information included and (this is the tricky part) the sum of all spending records for that account. The only way I can think of to do this would require 2 queries. Join accounts and spending records summing up all spending records for each account. The output of this query could be joined with the the planning info yielding the cartesian product we are looking for. I can't figure out how to get SQL to use the results of one query in another select without actually creating a temp table. We are using UNIFY/2000 SQL but I would hope any SQL solution would be portable :-). Any and all suggestions would be appreciated. -- Kevin Meek kmeek@cti.com
dberg@informix.com (David I. Berg) (11/21/90)
In article <322@cti1.UUCP> kmeek@cti1.UUCP (Kevin Meek) writes: >We want an output record for each planning record with the corresponding >account information included and (this is the tricky part) the sum >of all spending records for that account. > select account.account_number,account.account_information, planning.planning_data,sum(spending.spending_data) where spending.account_number = planning.account_number and planning.account_number = account.account_number group by account_number,account_information,planning_data