I have no idea if this will actually work but I believe that if we use the
documented behavior of "anonymous views" to encapsulate the UNION query,
we may be able to build a single two-stage query that could resolve your
issue. Please let the list know if this works or not. I do know of another
way you can do this if this doesn't work.
Select sum(numberOfC) as numberOfC
, country
, MIN(firstcustomer) as firstcustomer
, MAX(latestcustomer) as latestcustomer
FROM (
(
select count(*) as numberOfC
, Customer.country
, MIN(Customer.creationdate) as firstcustomer
, MAX(country.creationdate) as latestcustomer
FROM Customer
INNER JOIN VIP
ON Customer.id=VIP.customer and VIP.brand=2
WHERE Customer.creationdate > '1999-31-01'
GROUP BY country
)
union
(
select count(*) as numberOfC
, Customer.country
, MIN(Customer.creationdate) as firstcustomer
, MAX(country.creationdate) as latestcustomer
FROM Customer
INNER JOIN Catalog
ON Customer.id=Catalog.customer
and Catalog.brand=2
WHERE Customer.creationdate > '1999-31-01'
GROUP BY country
)
)
GROUP BY country
ORDER BY numberOfC desc;
ONE MORE THING --- I also noticed that in your original query you did not
list all of your non-aggregated columns in your GROUP BY clauses within
your UNION query. MySQL does not throw an error when you do this but the
SQL standard does not support this behavior and nearly every other
SQL-based product will throw you an error.
In your original query the third column is being filled with some random
value of creationdate from all of the rows that meet your where condition.
I am not confident that your query is returning useful information for
that column. You should either take the MIN() or MAX() of it (or both in
separate columns like I did) or just leave it out of the query.
Regards,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Jesper Goos <[EMAIL PROTECTED]> wrote on 08/19/2004 05:07:55 AM:
> Hi
>
> I'm having problems with a union and group by...
> My query looks like this...
>
> (select count(*) as numberOfC,Customer.country,Customer.creationdate
> FROM Customer,VIP where Customer.id=VIP.customer and VIP.brand=2 and
> Customer.creationdate > '1999-31-01' group by country)
> union
> (select count(*) as numberOfC,Customer.country,Customer.creationdate
> FROM Customer,Catalog where Customer.id=Catalog.customer and
> Catalog.brand=2 and Customer.creationdate > '1999-31-01' group by
country)
> order by numberOfC desc;
>
> My problem is, that the two gruop by's each give me one "numberOfC". Can
> I just add those? My goal is to get the destinct number of cutomers in
> each country...
>
> regars
> jesper
>
>
>
