Hello Guys,
I need to select a RATE records from database. The values are in float format. After my select i need to find an average value from the filtrated records. As you can see below I`ve got several values used to limit the range of query.
Here is my select query:
SELECT RATE FROM PRODUKCJA WHERE WAGA=:waga AND MASA >= :masa_od AND MASA <= :masa_do AND DATA >= Convert(DateTime, :data_od) AND DATA <= Convert(DateTime, :data_do) '+
'AND CZAS >= Convert(DateTime, :czas_od) AND CZAS <= Convert(DateTime, :czas_do)
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Proposed SELECT is wrong I think.
Try
SELECT AVG(RATE) FROM ...
If You need only average.
Can You explain WHERE clause in this select?
It looks very suspicious to me.
Do You want to select records from a period for example like this
from 23/11/2008 15:00 to 24/11/2008 13:00
The separatly comparing date and time parts is a mistake.
since there is no group by the select works just as well
you need to provide a date/time format in the convert function
look in the online books for help on CAST / CONVERT
the format for yyyy-mm-dd hh:mi:ss(24h) is 120 (ODBC canonical)
SELECT AVG(RATE)
FROM PRODKCJA
WHERE WAGA=:waga
AND MASA BETWEEN :masa_od AND :masa_do
AND DATA BETWEEN Convert(DateTime, :data_od, 120) AND Convert(DateTime, :data_do, 120)
AND CZAS BETWEEN Convert(DateTime, :czas_od, 120) AND CZAS <= Convert(DateTime, :czas_do, 120)
:data_od, :data_do, :czas_od and :czas_do
should be string parameters in format 'yyyy-mm-dd hh:mi:ss'
well this first I do to get the right values is build a query giving me all the information i need to do the calculations
without any sum, avg, min, max, but including the columns for these operations
the last step is basically a group by over the values that don't have to be aggregated
so:
select t1.field1, t1.field2, t1.field3, t2.field4, t2.field5
from table1 t1, table t2
where t1.fieldx = t2.fieldy
let's say field5 we need the avg for field5
select t1.field1, t1.field2, t1.field3, t2.field4, avg(t2.field5) avgfield5
from table1 t1, table t2
where t1.fieldx = t2.fieldy
group by t1.field1, t1.field2, t1.field3, t2.field4
I don't know if this is what you need
there are things missing to understand what you want ...
Business Accounts
Answer for Membership
by: Sharath_123Posted on 2008-11-24 at 17:00:51ID: 23031835
check this:
Select allOpen in new window