Geek Answers Handbook

SQL Columns not in SELECT

I have a table with three columns:

userid   mac_address   count

Entries for one user might look like this:

57193   001122334455   42
57193   000C6ED211E6   15
57193   FFFFFFFFFFFF   2

I want to create a view that displays only those MACs that are considered "commonly used" for this user. For example, I want to filter out MACs that use <10% compared to the most used MAC address for this user. In addition, I want 1 line per user. This can be easily achieved with GROUP BY, HAVING and GROUP_CONCAT:

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

Indeed, the result is as follows:

57193   001122334455,000C6ED211E6   42

However, I really don't need the count column in my opinion. But if I select this from the SELECT statement, I get the following error:

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

, , ? , , , , , .

!

+5
1

HAVING , , .
, , .

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:
- MySQL , , Oracle. . :

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)

userid macs, SELECT , .
, MySQL - MySQL ...

+7

More articles:


All Articles