Re: SQL help
Rick,
I assume cust_name is a unique column. Here's a solution that should
work:
select cust_name,
(select sum(sold_amt),sum(profit_amt) from invoices inv_mon
where cust_name=invoices.cust_name
and year(inv_dt)=1998 and month(inv_dt)=1),
sum(sold_amt),sum(profit_amt)
from invoices where year(inv_dt=1998) group by 1
The basic idea is to let the main query select for the year values and
the subquery select for the month values. The subquery must specify the
current customer of the main query - that's what you've been missing.
----------------------------------------------------------------------
John H. Frantz Power-4gl: Extending Informix-4gl
frantz@centrum.is http://www.rl.is/~john/pow4gl.html
Rick Walrath wrote:
> ...
> 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