The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 19, 2019, 06:13:02 AM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: Interesting query  (Read 5734 times)
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« on: April 24, 2007, 08:24:13 PM »

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
« Last Edit: April 24, 2007, 08:26:10 PM by thedarkness » Logged

"I want to be the guy my dog thinks I am."
 - Unknown
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #1 on: April 24, 2007, 09:03:51 PM »

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;

Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #2 on: April 24, 2007, 10:26:24 PM »

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* ROFLMAO ROFLMAO ROFLMAO

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
Logged

"I want to be the guy my dog thinks I am."
 - Unknown
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #3 on: April 25, 2007, 01:12:34 PM »

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

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #4 on: April 25, 2007, 01:33:02 PM »

lol. thats exactly why this forum was created though.

 Praise
« Last Edit: June 05, 2007, 06:10:38 PM by perkiset » Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #5 on: April 25, 2007, 04:32:40 PM »

Cool nuts,

Thanks for the tip. This forum rocks  Grin

Cheers,
td
Logged

"I want to be the guy my dog thinks I am."
 - Unknown
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #6 on: April 25, 2007, 04:35:44 PM »

Thanks for the tip. This forum rocks  Grin

 Praise
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
greenway
Rookie
**
Offline Offline

Posts: 12


View Profile
« Reply #7 on: April 26, 2007, 04:11:29 PM »

I am going to love this place.... Applause
Logged

No links in signatures please
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #8 on: April 26, 2007, 04:17:33 PM »

I am going to love this place.... Applause

 Praise

*starts to get dizzy*
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!