Reputation: 21322
I've more experience using Access where I would build up my analysis in small parts and query each new view.
I'm not trying to do something that must be simple in SQL.
If I have a query of the format:
SELECT events.people, COUNT(events.eventType) AS usersAndEventCount
FROM theDB.events
WHERE event_id = 884
GROUP BY people
And I then want to query usersAndEventCount like so:
Select usersAndEventCount.people, usersAndEventCount.events
FROM [from where actually?]
Tried from:
usersAndEventCount;
events
theDB.events
This must seem very basic to SQL users on SO. But in my mind it's much easier to breakdown the larger query into these sub queries.
How would I query usersAndEventCount in the same query?
Upvotes: 0
Views: 97
Reputation: 175
I am not sure per 100%, because at the moment i can not test. But I think it should work. uaec is the alias for the subquery. This alias you can use in the main query
Select uaec.people, uaec.usersAndEventCount
FROM (SELECT events.people, COUNT(events.eventType) AS usersAndEventCount
FROM theDB.events
WHERE event_id = 884
GROUP BY people) uaec
Upvotes: 1
Reputation: 25371
In pure SQL, you can use nested queries (AKA sub-queries). Just enclose your first query in ()
brackets, so your query will look like this:
Select usersAndEventCount.people, usersAndEventCount.events
FROM (SELECT events.people, COUNT(events.eventType) AS events
FROM theDB.events
WHERE event_id = 884
GROUP BY people) usersAndEventCount
Alternatively, to save the first query and reuse it in several places like you were doing in Access, you can save it as a View or Stored Procedure depending on the database system you're using. If you want an example, let me know the database system you're using.
Upvotes: 3
Reputation:
Your statement "then want to query usersAndEventCount" does not make sense because usersAndEventCount
is a column - at least in your first example. You can not "query" a column.
But from the example you have given it seems you want something like this:
Select usersAndEventCount.people, usersAndEventCount.events
FROM (
SELECT events.people,
COUNT(events.eventType) AS as events
FROM theDB.events
WHERE event_id = 884
GROUP BY people
) as usersAndEventCount
This is called a "derived table" in SQL
Upvotes: 4