CODEWITHSUNDEEP
sql
Doug Fir
Doug Fir

Reputation: 21322

Querying a query

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

Answers (3)

OliC
OliC

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

Racil Hilan
Racil Hilan

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

user330315
user330315

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

Related Questions