SELECT a column not in GROUP BY clause

Question

I'm trying to write a query that would select only the rows that have events which where the only events in that year.

Eg:

Year   Event
2011     A
2011     B
2012     C
2013     B
2013     D
2014     D

So, I would like to get the rows 2012 C and 2014 D in the results. I tried doing a GROUP BY on Year, but that wouldn't let me select the Event column. 2011 and 2013 have 2 events, so these shouldn't be in the results.

Please help.

EDIT: I could write a nested query to get the only the rows having count(Year) = 1 with GROUP BY Year, but I'm unable to get the Event column selected in the outer query

SELECT Year, Event from table where Year in (SELECT Year from table GROUP BY Year Having count(*) = 1) as count;

Answer 1

There is no need for using a subquery or nested query. You can simply GROUP By Year field and use HAVING COUNT(Year)=1 to find the required rows. So, the applicable query will be:

SELECT Year, Event
FROM table_name
GROUP BY Year
HAVING COUNT(Year)=1

You can find the executable solution sample at:

http://sqlfiddle.com/#!9/b47044/11

Logic: When you group by Yearit aggregates all rows with same year. So, count will be 2 for 2011.

You can check this by running:

SELECT Year, Event, COUNT(Year) as event_count
FROM table_name
GROUP BY Year

You can see this intermediate step in execution, at: http://sqlfiddle.com/#!9/b47044/10

This above solution will only work for MySQL version < 5.7. For higher versions find the solution below.

For 5.7 and greater the ONLY_FULL_GROUP_BY SQL mode is enabled by default so this will fail. Either you can update this mode( Refer answers under SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by ) or alternatively you can use ANY_VALUE() function to refer to the non-aggregated column, so update query that will work in MySQL 5.7 and greater is:

SELECT Year, ANY_VALUE(Event)
FROM table_name
GROUP BY Year
HAVING COUNT(Year)=1;

You can find executable example at: https://paiza.io/projects/e/tU-7cUoy3hQUk2A7tFfVJg

Reference: https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/miscellaneous-functions.html#function_any-value