Aggregation query

[christinesam]

"pick one query that requires the use of aggregation (min, max, average, or count are all fine)."

Count(#comments) user left Or Count(#movies) an actor has appeared in

getting amount of comments a user left:

select count(id) from users u, comments c where u.accountname = c.accountname and u.accountname = 'foobar';

getting movie count for an actor:

This one is only getting the count of movies: select count(title) as moviecount from acts_in a where a.name = 'QueryString1' and a.birthday = 'QueryString2';

This one return not only the count of movies but the info of that actor: *select from (select a.name, a.birthday, a.nationality, count(t.title) as moviecount from actor a, acts_in t where a.name = t.name and a.birthday = t.birthday group by a.name, a.birthday, a.nationality) temp where temp.name = 'Daniel Radcliffe' and temp.birthday = '1989-07-23';**

[blindacai]

Save simple aggregation code here:
String query = "select * from (select a.name, a.birthday, a.nationality, count(t.title) as MovieCount" + "from actor a, acts_in t where a.name = t.name and a.birthday = t.birthday + " + "group by a.name, a.birthday) temp" + "where temp.name = " + Query.formatVar(name) + " " + "and temp.birthday = " + Query.formatVar(birthday);

[christinesam]

FINISHED!