Interesting query

To The Live Thread

Thread: Interesting query

Back to category: Database Stuff

thedarkness

I just put this together to see what days we had the most enquiries on, thought someone else might be interested. It's for Postgres but should be easy enough to get going in other dbs.

select count(*), enquiry_date from enquiries where enquiry_date
in (select distinct enquiry_date from enquiries) group by enquiry_date order by count(*) desc;

Cheers,
td

nutballs

why do you have the sub-select in there? it looks redundant from what I can tell?

this should give the same exact result?

select count(*), enquiry_date from enquiries group by enquiry_date order by count(*) desc;

thedarkness

quote author=nutballs link=topic=98.msg452#msg452 date=1177473831

why do you have the sub-select in there? it looks redundant from what I can tell?

Coz I'm an idiot? Coz I don't understand why I don't need it? *goes back to SQL manual* Applause

quote author=nutballs link=topic=98.msg452#msg452 date=1177473831

this should give the same exact result?

select count(*), enquiry_date from enquiries group by enquiry_date order by count(*) desc;

Indeed it does, thanks nuts.

Cheers,
td

nutballs

lol. thats exactly why this forum was created though.

well your sub-query example would be for if you wanted to refine the results from 1 table, based on the results of another query. When you are only pulling results from 1 table (or even tables joined) there is no reason for a subquery generally.

an example of why you would do something like you did would be this (made up table name and such):

select count(*), enquiry_date from enquiries where userID in
(select distinct userID from users where lastlogin>'3/1/07')
group by enquiry_date order by count(*) desc;

so your first grabbing all the userIDs of users who have logged in since march 1st of this year, and then counting all the queries that each of those users made. so you would use this for example to determine query-quotas and if someone is abusing the system.

perkiset

quote author=nutballs link=topic=98.msg514#msg514 date=1177531954

lol. thats exactly why this forum was created though.