Re: SQL help

Douglas Wilson wrote in message <6a7n0e$ggf$1@gte2.gte.net>... >Rick Walrath wrote: >> >> I am a v5.1 trying to run a query that will contain a subquery utilizing the >> sum(field) function. It goes a little something like this... >> >> select >> cust_name, >> (select sum(sold_amt) from invoices >> where MONTH(inv_date)=1 and YEAR(inv_date)=1998), >> (select sum(profit_amt) from invoices >> where MONTH(inv_date)=1 and YEAR(inv_date)=1998)) >> from >> invoices >> group by 1 >try: >select >cust_name, >sum(sold_amt), >sum(profit_amt) >from >invoices >where MONTH(inv_date)=1 and YEAR(inv_date)=1998 >group by 1 > >Hope that helps, >Douglas Wilson What if I want to take the query another step further, to return both MTD and YTD information......... select cust_name, (select sum(sold_amt) from invoices where MONTH(inv_dt)=1 and YEAR(inv_dt)=1998), (select sum(profit_amt) from invoices where MONTH(inv_dt)=1 and YEAR(inv_dt)=1998), (select sum(sold_amt) from invoices where YEAR(inv_dt)=1998), (select sum(profit_amt) from invoices where YEAR(inv_dt)=1998) from invoices group by 1 ..........that is the query I ran that returned the sales and profit totals of ALL customers with each customer name field, rather than just that customers totals. I tried selecting the cust_nm in each subquery, but received the error message "Error -574 during prepare: A subquery has returned not exactly one column". Thanks for the help rick