CODEWITHSUNDEEP
sqlsql-servergroup-byaverage
jacobronniegeorge
jacobronniegeorge

Reputation: 555

AVG and GROUP BY

I have a set of records from which I select and average various data, and group by the hour. It seems as if this function/query does an excellent job of averaging as I am getting 744 records with no problems. However from June 8 to June 16 it seems to skip the averaging and group by. As massive as the data is I have combed through trying to find a reason why this might occur. It seems as if the data in those rows are no different than the data in the other rows and there should be no reason for skipping of records from June 8 to June 16. There are NULLS, but I would assume AVG would just skip this data? Has anyone seen this particular pattern of skipping records especially as it pertains to the AVG and GROUP BY function?

DATE FORMAT: YYYY-MM-DD HH:MM:SS.000 DB Manager: SQL Server Management Studio by Microsoft Corporation

Working Data Link(this data was averaged properly): https://docs.google.com/spreadsheet/ccc?key=0Av--kLXVY_a3dHA5UjBKYXc3UmRhQW9RN0NGbXl1NHc

Non-Working Data Link( this data was completely skipped for some reason): https://docs.google.com/spreadsheet/ccc?key=0Av--kLXVY_a3dFZqX3RuYVNWYWpVemNmSS12bzhIOXc

Query used to for averaging and groupby:

`INSERT INTO AverageRawData(DateTime,
SCR1_EXHAUST_GAS_TEMP,SCR1_NOX_LEVEL,SCR1_NH3_FLOW_RATE,SCR1_O2_LEVEL,SCR1_NOx,
SCR2_EXHAUST_GAS_TEMP,SCR2_NOX_LEVEL,SCR2_NH3_FLOW_RATE,SCR2_O2_LEVEL,SCR2_NOx,
SCR3_EXHAUST_GAS_TEMP,SCR3_NOX_LEVEL,SCR3_NH3_FLOW_RATE,SCR3_O2_LEVEL,SCR3_NOx,
SCR4_EXHAUST_GAS_TEMP,SCR4_NOX_LEVEL,SCR4_NH3_FLOW_RATE,SCR4_O2_LEVEL,SCR4_NOx,
CAT1_FUEL_FLOW_RATE,CAT2_FUEL_FLOW_RATE,CAT3_FUEL_FLOW_RATE,
VOLVO1_FUEL_FLOW_RATE,VOLVO2_FUEL_FLOW_RATE,VOLVO3_FUEL_FLOW_RATE,VOLVO4_FUEL_FLOW_RATE,VOLVO5_FUEL_FLOW_RATE,
TOTAL_NOx)

SELECT MIN(DateTime),
AVG(Convert(decimal(10,3),SCR1_EXHAUST_GAS_TEMP)),
AVG(Convert(decimal(10,3),SCR1_NOX_LEVEL)),
AVG(Convert(decimal(10,3),SCR1_NH3_FLOW_RATE)),
AVG(Convert(decimal(10,3),SCR1_O2_LEVEL)),
AVG(Convert(decimal(10,3),SCR1_NOx)),
AVG(Convert(decimal(10,3),SCR2_EXHAUST_GAS_TEMP)),
AVG(Convert(decimal(10,3),SCR2_NOX_LEVEL)),
AVG(Convert(decimal(10,3),SCR2_NH3_FLOW_RATE)),
AVG(Convert(decimal(10,3),SCR2_O2_LEVEL)),
AVG(Convert(decimal(10,3),SCR2_NOx)),
AVG(Convert(decimal(10,3),SCR3_EXHAUST_GAS_TEMP)),
AVG(Convert(decimal(10,3),SCR3_NOX_LEVEL)),
AVG(Convert(decimal(10,3),SCR3_NH3_FLOW_RATE)),
AVG(Convert(decimal(10,3),SCR3_O2_LEVEL)),
AVG(Convert(decimal(10,3),SCR3_NOx)),
AVG(Convert(decimal(10,3),SCR4_EXHAUST_GAS_TEMP)),
AVG(Convert(decimal(10,3),SCR4_NOX_LEVEL)),
AVG(Convert(decimal(10,3),SCR4_NH3_FLOW_RATE)),
AVG(Convert(decimal(10,3),SCR4_O2_LEVEL)),
AVG(Convert(decimal(10,3),SCR4_NOx)),
AVG(Convert(decimal(10,3),CAT1_FUEL_FLOW_RATE)),
AVG(Convert(decimal(10,3),CAT2_FUEL_FLOW_RATE)),
AVG(Convert(decimal(10,3),CAT3_FUEL_FLOW_RATE)),
AVG(Convert(decimal(10,3),VOLVO1_FUEL_FLOW_RATE)),
AVG(Convert(decimal(10,3),VOLVO2_FUEL_FLOW_RATE)),
AVG(Convert(decimal(10,3),VOLVO3_FUEL_FLOW_RATE)),
AVG(Convert(decimal(10,3),VOLVO4_FUEL_FLOW_RATE)),
AVG(Convert(decimal(10,3),VOLVO5_FUEL_FLOW_RATE)),
AVG(Convert(decimal(10,3),TOTAL_NOx))
FROM jmusa_LOG1
GROUP BY DATEPART(DD,DateTime),DATEPART(HH,DateTime)`

Upvotes: 0

Views: 499

Answers (1)

Roberto Navarro
Roberto Navarro

Reputation: 958

Assuming you're working with an Oracle DB, try performing an NVL(R1_EXHAUST_GAS_TEMP,0) on all the columns where you see no data.

This is from docs.oracle:

All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.

As they suggest, using NVL should fix your issue.

Link: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm

Upvotes: 1

Related Questions