next »

[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.

Code:

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?

Code:

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

[thedarkness]

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*

this should give the same exact result?

Code:

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):

Code:

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]

lol. thats exactly why this forum was created though.

 

[thedarkness]

Cool nuts,

Thanks for the tip. This forum rocks 

Cheers,
td

[perkiset]

Thanks for the tip. This forum rocks 

 

[greenway]

I am going to love this place....

[perkiset]

I am going to love this place....

 

*starts to get dizzy*