Thread: Aggregates
Hi, If I have a table with users and a table with messages, is it possible to have a query that returns user.* as well as one extra column with the number of messages they have posted and the data and time of the last message? At the moment I am using a subquery to do this, however it seems suboptimal. Is there a better way? - Naz.
Naz Gassiep wrote: > Hi, > If I have a table with users and a table with messages, is it > possible to have a query that returns user.* as well as one extra column > with the number of messages they have posted and the data and time of > the last message? At the moment I am using a subquery to do this, > however it seems suboptimal. Is there a better way? Not really. You have three separate queries really: 1. User details 2. Total number of messages posted 3. Details on last message posted Unless you have a messaging-summary table that you keep up-to-date with triggers you're looking at a three-part query. -- Richard Huxton Archonet Ltd
On Jun 21, 2007, at 09:22, Richard Huxton wrote: > Naz Gassiep wrote: >> Hi, >> If I have a table with users and a table with messages, is it >> possible to have a query that returns user.* as well as one extra >> column >> with the number of messages they have posted and the data and time of >> the last message? At the moment I am using a subquery to do this, >> however it seems suboptimal. Is there a better way? > > Not really. You have three separate queries really: > 1. User details > 2. Total number of messages posted > 3. Details on last message posted > > Unless you have a messaging-summary table that you keep up-to-date > with triggers you're looking at a three-part query. Certainly except for the user details it could be a single GROUP BY with several aggregate functions, something like: select user.userid, count(*), max(message.datetime) from user join message using (userid) group by user.userid; But if userid is UNIQUE, then so is user.*. You can't do something like GROUP BY USER.*, but you can group by all the user columns you're actually interested in selecting: select userid, user.name, user.address, count(*), max (message.datetime) from user join message using (userid) group by userid, user.name, user.address; As to whether this is faster or prettier than a subquery, I dunno. - John D. Burger MITRE
John D. Burger wrote: > > On Jun 21, 2007, at 09:22, Richard Huxton wrote: > >> Naz Gassiep wrote: >>> Hi, >>> If I have a table with users and a table with messages, is it >>> possible to have a query that returns user.* as well as one extra column >>> with the number of messages they have posted and the data and time of >>> the last message? At the moment I am using a subquery to do this, >>> however it seems suboptimal. Is there a better way? >> >> Not really. You have three separate queries really: >> 1. User details >> 2. Total number of messages posted >> 3. Details on last message posted >> >> Unless you have a messaging-summary table that you keep up-to-date >> with triggers you're looking at a three-part query. > > Certainly except for the user details it could be a single GROUP BY with > several aggregate functions, something like: > > select user.userid, count(*), max(message.datetime) > from user join message using (userid) > group by user.userid; Ah, but this just includes the time of the last message, not its data. -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Ah, but this just includes the time of the last message, not its data. Oops, I read the OP's question as "date and time", rather than "data and time". Nevermind. :) - John D. Burger MITRE