mongolol mongolol - 2 months ago 8
SQL Question

Error when Attempting to Group and Alias Data

Relatively new to SQL and have been trying to return the min, average, and max of the number of events that people attended.

Essentially, I have a table titled titled

all
which I am generating a view
events
from. The view
events
contains the columns
id
and
event
from this year. I was attempting to group the counts of events by person and give this selection the label of
event count,
but for some reason am continuously receiving a parsing error. (This is being done in HIVE.)

--VIEW CREATION:
CREATE VIEW events
AS
SELECT all.id, all.event
FROM all
WHERE all.year = 2016;

--ATTEMPTED CALCULATION WHICH IS FAILING
SELECT avg(event_count), min(event_count), max(event_count)
FROM (SELECT COUNT(events.event), events.id FROM events
GROUP BY events.id) AS event_count;

Answer

Is this what you want?

SELECT avg(e.event_count), min(e.event_count), max(e.event_count)
FROM (SELECT COUNT(e.event) as event_count, e.id
      FROM events e
      GROUP BY e.id
    ) e;

As I mention in the comment, you are confusing table aliases and column aliases. Also, it seems strange that a field called id would be repeated in a table. That seems like an awkward data structure.

Comments