How can I get the contents of these two queries into one table? I'm
interested in having a table with four columns:

C.CustName
YTDSales
LYTDSales
LastDate

select C.CustName, sum(I1.Amount) as YTDSales, max(I1.InvDate) as
LastDate, I1.CustID
from Customer C
join Invoice I1 on (I1.CustID=C.CustID)
group by I1.CustID having (I1.InvDate >= '2006-01-01');

select C.CustName, sum(I2.Amount) as LYTDSales, I2.CustID, I2.InvDate
from Customer C
join Invoice I2 on (I2.CustID=C.CustID)
group by I2.CustID having ((I2.InvDate >= '2005-01-01') and
(I2.InvDate < '2006-01-01'));

--Bruce

"Bruce" <bvanderweb@gmail.com> wrote in message
news:fp66m2tbb2lppsmugoi7kki7bdbkg1trp8@4ax.com...
> How can I get the contents of these two queries into one table? I'm
> interested in having a table with four columns:
>
> C.CustName
> YTDSales
> LYTDSales
> LastDate
>

SELECT INTO MEMORY\TEMP
CUSTId,
SUM(IF(InvDate BETWEEN '2005-01-01' AND '2005'12'31' THEN Amount ELSE
CAST(0 AS DECIMAL(0, 4) LYTDSales,
SUM(IF(InvDate >= '2006-01-01' THEN Amount ELSE CAST etc) YTDSales,
MAX(InvDate) LastDate
FROM Invoices
GROUP BY CustID;
CREATE INDEX BYCUSTOMER ON MEMORY\TEMP(CUSTID);
SELECT xxx FROM CUSTOMER C
JOIN MEMORY\TEMP T ON C.CUSTid = T.CUSTid;

In my experience, a script works better for this type query. First select
from invoices into memory grouping by customer id, then JOIN this memory
table with the customer table for the final presentation to the user. Faster
and cleaner, also you prevent a problem like in your second query where you
are both selecting individual fields and GRUPING on other fields, in general
a big no-no. For aggregate functions, you should only select the aggregate
fields and the fields that are part of the GROUP BY. Nothing else.

Also I think (not sure) that you confuse HAVING and WHERE. Your second query
IMO should use WHERE date etc.

Robert

> select C.CustName, sum(I1.Amount) as YTDSales, max(I1.InvDate) as
> LastDate, I1.CustID
> from Customer C
> join Invoice I1 on (I1.CustID=C.CustID)
> group by I1.CustID having (I1.InvDate >= '2006-01-01');
>
> select C.CustName, sum(I2.Amount) as LYTDSales, I2.CustID, I2.InvDate
> from Customer C
> join Invoice I2 on (I2.CustID=C.CustID)
> group by I2.CustID having ((I2.InvDate >= '2005-01-01') and
> (I2.InvDate < '2006-01-01'));
>
> --Bruce
>

Bruce


Unless you put them into intermediate tables and then query those to join them you can't.

I was going to suggest merging them into a single query but I notice the GROUP BY clause will prevent that.

My only suggestion is to SELECT INTO memory tables and then join those.

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:3E3BDB05-A1C8-4D88-850F-CCAF634B40A5@news.elevatesoft.com...
> Bruce
>
>
> Unless you put them into intermediate tables and then query those to join
> them you can't.
>
> I was going to suggest merging them into a single query but I notice the
> GROUP BY clause will prevent that.
>

Where in the help file did you read that ? I guess you are talking about the
union statement?

"The WHERE, GROUP BY, HAVING, LOCALE, ENCRYPTED, and NOJOINOPTIMIZE clauses
can be specified for all or some of the individual SELECT statements being
joined with a UNION/EXCEPT/INTERSECT clause.  "

Frans


I can't think of any way to reconcile two different GROUP BY clauses from two different queries into a single query

group by I1.CustID having (I1.InvDate >= '2006-01-01');
group by I2.CustID having ((I2.InvDate >= '2005-01-01') and (I2.InvDate < '2006-01-01'));


Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:BC0D2CC4-0C9B-4A7C-90BE-BF0209804B6D@news.elevatesoft.com...
> Frans
>
>
> I can't think of any way to reconcile two different GROUP BY clauses from
> two different queries into a single query
>
> group by I1.CustID having (I1.InvDate >= '2006-01-01');
> group by I2.CustID having ((I2.InvDate >= '2005-01-01') and (I2.InvDate <
> '2006-01-01'));
>

I already posted in this thread how to do that. You don't use HAVING, you
select the data with IF and/or WHERE clauses.

Robert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:BC0D2CC4-0C9B-4A7C-90BE-BF0209804B6D@news.elevatesoft.com...
> Frans
>
>
> I can't think of any way to reconcile two different GROUP BY clauses from
> two different queries into a single query
>
> group by I1.CustID having (I1.InvDate >= '2006-01-01');
> group by I2.CustID having ((I2.InvDate >= '2005-01-01') and (I2.InvDate <
> '2006-01-01'));
>
>
> Roy Lambert
>
Roy,

aarrggg, my mistake, this was no question with union as answer