any SQL QUERY masters? 1

[pgstein]

Hi, I am trying to write a query using SQL. I have two tables that are as below:

table miscdata contains: day, heatid

What I am trying to do is to get the number of heatids for a date range. I can get it to return the count of all the heatids for the range, but I need a row for each day in the range.

right now my query is:
SELECT count(heatid)
FROM miscdata
WHERE day = (SELECT DISTINCT day FROM dbo_MISCDATA WHERE day between day1 and day2);

It doesn't work, but that is kind of what I am trying to do.

If anyone has any suggestions I would be very appreciative. Sorry if the isnt very clear.

 

[PHV]

SELECT day, COUNT(heatid)
FROM miscdata
WHERE day BETWEEN day1 AND day2
GROUP BY day

BTW, isn't day a reserved keyword ?


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rac2]

PHV,

Nice point BTW. I thought possibly it was not, maybe just a SQL Server keyword. I looked for the answer in the forum FAQ faq220-1073 by tlbroadbent and found that it is.

<reserved word> ::=
ABSOLUTE | ACTION | . . . | CURSOR
| DATE | DAY | DEALLOCATE | DEC | DECIMAL | DECLARE | DEFAULT | DEFERRABLE . . .

But along the way I found something almost funny

<key word> ::=
<reserved word>
| <non-reserved word>

 

[PHV]

LOL

 

[PHV]

Sorry, I wanted to say; ROTFL

 

[pgstein]

hmmm...thats interesting. it works though, so I guess Ill leave it. Thanks for your help, im sure Ill have more ?s later.

 

[JarlH]

FYI, the reserved words in SQL-92, SQL-99 and SQL-2003 can be found here:

http://developer.mimer.com/validator/sql-reserved-words.tml