Welcome to WebmasterWorld Guest from 220.127.116.11
Forum Moderators: open
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)
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?
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]
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.
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)
(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!
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.