SQL Having on columns not in SELECT

Question

I have a table with 3 columns:

userid   mac_address   count

The entries for one user could look like this:

57193   001122334455   42
57193   000C6ED211E6   15
57193   FFFFFFFFFFFF   2

I want to create a view that displays only those MAC's that are considered "commonly used" for this user. For example, I want to filter out the MAC's that are used <10% compared to the most used MAC-address for that user. Furthermore I want 1 row per user. This could easily be achieved with a GROUP BY, HAVING & GROUP_CONCAT:

SELECT userid, GROUP_CONCAT(mac_address SEPARATOR ',') AS macs, count
FROM mactable
GROUP BY userid
HAVING count*10 >= MAX(count)

And indeed, the result is as follows:

57193   001122334455,000C6ED211E6   42

However I really don't want the count-column in my view. But if I take it out of the SELECT statement, I get the following error:

#1054 - Unknown column 'count' in 'having clause'

Is there any way I can perform this operation without being forced to have a nasty count-column in my view? I know I can probably do it using inner queries, but I would like to avoid doing that for performance reasons.

Your help is very much appreciated!

Daniel Hilgarth · Accepted Answer · 2011-07-14T12:32:12.693

12

As HAVING explicitly refers to the column names in the select list, it is not possible what you want.
However, you can use your select as a subselect to a select that returns only the rows you want to have.

SELECT a.userid, a.macs
FROM
(
    SELECT userid, GROUP_CONCAT(mac_address SEPARATOR ',') AS macs, count
    FROM mactable
    GROUP BY userid
    HAVING count*10 >= MAX(count)
) as a

UPDATE:
Because of a limitation of MySQL this is not possible, although it works in other DBMS like Oracle. One solution would be to create a view for the subquery. Another solution seems cleaner:

CREATE VIEW YOUR_VIEW (userid, macs) AS
SELECT userid, GROUP_CONCAT(mac_address SEPARATOR ',') AS macs, count
FROM mactable
GROUP BY userid
HAVING count*10 >= MAX(count)

This will declare the view as returning only the columns userid and macs although the underlying SELECT statement returns more columns than those two.
Although I am not sure, whether the non-DBMS MySQL supports this or not...

edited Jul 14 '11 at 12:32

answered Jul 14 '11 at 11:50

Daniel Hilgarth

171,043
40
335
443

I didn't know subselects could be used like that. Thanks a lot, just the answer I was looking for! – Rapsey Jul 14 '11 at 11:59
However, I ran into the following: #1349 - View's SELECT contains a subquery in the FROM clause Any ideas? – Rapsey Jul 14 '11 at 12:20
@Rapsey: No, because you never told us, what DBMS you are using. – Daniel Hilgarth Jul 14 '11 at 12:21
My apologies. I am using MySQL 5.1.41 – Rapsey Jul 14 '11 at 12:23
@Rapsey: See update. I added the alias based in the information provided in the [documentation](http://dev.mysql.com/doc/refman/5.0/en/from-clause-subqueries.html). – Daniel Hilgarth Jul 14 '11 at 12:26
@Rapsey: This possibly still doesn't work in MySQL because of a limitation. You can work around this issue by creating a view with the part of the sub select and use this view in the main view. However, this seems like overkill just to remove an unwanted column... – Daniel Hilgarth Jul 14 '11 at 12:28
1

Naming the subselect doesn't help in MySQL. After trying your update I get the following: #1353 - View's SELECT and view's field list have different column counts Creating a second view to select from the first view does work. – Rapsey Jul 14 '11 at 13:23

SQL Having on columns not in SELECT

1 Answers1