SELECT statement help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT statement help

Author

Topic

friskyweasel
Starting Member

2 Posts

Posted - 2004-07-02 : 16:45:58

hello all
say i have the following scenario:

sql statement #1:
SELECT DISTINCT ORIGIN FROM RESULTS WHERE (STAMP >= '7/1/2004' AND STAMP <='7/2/2004')

this obviously returns a result set containing all unique values in the ORIGIN field

so now that I know all the unique origins, I could then go down that list of origins returned and construct a series of statements like this:
SELECT COUNT(*) FROM RESULTS WHERE (STAMP >= '7/1/2004' AND STAMP <='7/2/2004') AND ORIGIN = 'origin1'
SELECT COUNT(*) FROM RESULTS WHERE (STAMP >= '7/1/2004' AND STAMP <='7/2/2004') AND ORIGIN = 'origin2'
SELECT COUNT(*) FROM RESULTS WHERE (STAMP >= '7/1/2004' AND STAMP <='7/2/2004') AND ORIGIN = 'origin3'

i'm obviously not an extremely efficient SQL query builder, so my question is, how could i execute ONE SQL statement against the table results that would return a 2 column result set containing each unique origin (only once), and the corresponding number of rows that have that origin value

hope my question makes sense - thanks in advance for any help you could provide

gwhiz
Yak Posting Veteran

78 Posts

Posted - 2004-07-02 : 17:01:15

Try this.

SELECT DISTINCT ORIGIN, count(*) FROM RESULTS WHERE (STAMP >= '7/1/2004' AND STAMP <='7/2/2004')
GROUP BY ORIGIN

friskyweasel
Starting Member

2 Posts

Posted - 2004-07-02 : 17:08:45

gwhiz
that is EXACTLY what i needed - i know some of these questions might seem rookie to you guys, but your help is GREATLY appreciated - i'm primarily an ASP developer - do a lot of work with database driven web sites, but 95% of all my tasks can be completed with simple SELECT, INSERT, UPDATE, DELETE statements - it's nice to know there are helpful people out there that can get you out of a jam - thanks a million man - i'm really hoping to increase my sql efficiency using this forum
great forum!
thanks again

quote:
Originally posted by gwhiz

Try this.

SELECT DISTINCT ORIGIN, count(*) FROM RESULTS WHERE (STAMP >= '7/1/2004' AND STAMP <='7/2/2004')
GROUP BY ORIGIN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources