MySQL, UNION, and GROUP BY error

Trying to combine two select statements

[erb5]

I'm trying to combine two select statements via a UNION and am having no luck. I want to get the total number of contacts submitted each month and the number of contacts submitted within the same number of days which have passed thus far in the current month. In other words, if it's the 15th day of the month, it will get the total contacts received during the first 15 days of each prior month.

I'm getting the GROUP BY error # 1140 despite the fact that my query has a group by clause.

SELECT Year(c1.date_received) AS yr,Month(c1.date_received) AS mnth,COUNT(c1.ct_ID) AS ttl_contacts 
FROM Contacts AS c1 
WHERE Day(c1.date_received) <= Day(CURRENT_TIMESTAMP) 
UNION
SELECT Year(c2.date_received) AS yr, Month(c2.date_received) AS mnth, COUNT(c2.ct_ID) AS ttl_contacts
FROM Contacts as c2
GROUP BY year(c2.date_received), month(c2.date_received) 
ORDER BY c2.date_received

Returns: "#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause"

I've tried it with and without the table aliases, FWIW. I think the table design is fairly obvious from the select statements, so I'll skip additional details about that.

Individually, both SELECT statements work as expected, so I considered just trying to combine the data after the fact, but it isn't my first choice for ease of output. I'm certainly open to any and all suggestions, however, if that makes more sense. Perhaps I'm approaching the problem completely wrong?

TIA!

[FalseDawn]

The first part of your statement (before the UNION) contains an aggregate function (COUNT), but no GROUP BY clause.

As the error says, this is illegal syntax.

Edit: If you haven't guessed - to clarify, this means that EACH statement in a UNION should be considered a separate entity - the GROUP BY does not apply globally to the whole statement.

[edited by: FalseDawn at 7:03 pm (utc) on Dec. 15, 2006]

[erb5]

Thank you for the explanation! Man alive, I read the same instructions over and over but I just didn't get it.

I'm still having issues getting the data I want, so additional insight is appreciated. Now the ORDER BY is giving me headaches, but more importantly, it's not pulling both contact counts they way I was hoping...

Anyway, thank you again; I will keep plugging away.

[phranque]

try putting

GROUP BY c1.ct_ID

in the first SELECT and

GROUP BY c2.ct_ID

in the second SELECT.

[erb5]

phranque, I tried your suggestion, but the output isn't what I was expecting; perhaps I misunderstood the suggestion? I do want the contact totals grouped by the year and month. I can post the statement I created based on your suggestion if it would help clarify things.

I can't figure out if I need the UNION or just a JOIN, although I'm not sure how to JOIN two tables and only have half of what I'm SELECTing affected by a WHERE clause (only the "contacts to date" should be affected). A larger part of my problem may be I am not explaining what I want well!

This is where I'm "at"; I guessed I may need a "filler" column in each SELECT if I use the UNION so that I can output the columns I want.

(SELECT Year(c1.date_received ) AS yr, 
Month( c1.date_received ) AS mnth, 
COUNT( c1.ct_ID ) AS ttl_contacts_todate,
COUNT( c1.ct_ID ) AS ttl_contacts
FROM Contacts AS c1
WHERE DAY (c1.date_received) <= DAY (CURRENT_TIMESTAMP)
GROUP BY year(c1.date_received) , month(c1.date_received)
)
UNION
(SELECT Year( c2.date_received ) AS yr, 
Month( c2.date_received ) AS mnth, 
NULL AS ttl_contacts_todate,
COUNT( c2.ct_ID ) AS ttl_contacts
FROM Contacts AS c2
GROUP BY year( c2.date_received ) , month(c2.date_received )
)

But everything is affected by the WHERE clause in the first SELECT. If I reverse the order, nothing is affected and it gets all the contacts per month. Regardless, both ttl_contacts_todate and ttl_contacts are the same, which is not what I want.

Again, still working; suggestions appreciated. If I ever come up with a solution, I will certainly post!

[iateadonut]

It's best to combine the data that you need into a new temporary table, and then make your select statement on that table.

For example,

create table tmp select customer, weight from logs.export;
insert into tmp (customer, weight) select customer, weight from logs.seaair;
insert into tmp (customer, weight) select customer, weight from logs.import;

in this case, i need to take customers out of three logs, import, export, and sea air. each log has details of every shipment they have made. since a single customer may be in all three logs, i need aggregate data from all three.

once that's finished, then i can do my select statement:

select customer, weight from tmp group by customer order by weight desc;

that will give me a list of my customers, the total weight of all their shipments, and their order from my highest volume customer to my lowest.

this was the common way to resolve all union problems before mysql 4.0, when the union function was added. it is still the best way to solve this type of problem when you need to group.