asked on

Null value is eliminated by an aggregate or other SET operation

INSERT INTO dbo.[BILLING.DEFAULT] (ACCOUNT_ID, ACCOUNT_NAME, TOTAL_MINUTES, TOTAL_PRICE)
Hi All,
with support of "Experts Exchanhe" Members i have the following "WORKIING" query

SELECT
[dbo].[BILLING.PLAN].ACCOUNT_ID,
[dbo].[BILLING.PLAN].ACCOUNT_NAME,
SUM([dbo].[BILLING.PLAN].CALL_DURATION) / 60 AS TOTAL_MINUTES,
CAST(SUM([dbo].[BILLING.PLAN].TOTAL_PRICE_PER_CALL) AS DECIMAL(27, 2)) AS TOTAL_PRICE
FROM [dbo].[BILLING.PLAN]
GROUP BY [dbo].[BILLING.PLAN].ACCOUNT_ID, [dbo].[BILLING.PLAN].ACCOUNT_NAME

My problem is comming from calcualtions. I hava a positive data into CALL.DURATION and TOTAL_PRICE_PER_CALL.

Please help me to avoud the resutls like:

Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)

ACCOUNT_ID ACCOUNT_NAME TOTAL_MINUTES TOTAL_PRICE
NULL BOB -21201271 -21237349.27

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

batman_k

ASKER

Hi Qlemo,
do i need to create columns
Reccords, Min Duration and Max Duration?
somwhere between records, min and max is the error

Msg 121, Level 15, State 1, Line 2
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

Qlemo

That is thought to be used without the INSERT, only the SELECT (as shown). We don't want the values to be inserted yet.

SOLUTION

Bhavesh Shah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bhavesh Shah

Please make a note,forget to put avoid word above

still if u want to run query and want to "avoid" that warning message
use ISNULL

batman_k

ASKER

Hi All,

Qlemo
................
ACCOUNT_ID      ACCOUNT_NAME      TOTAL_MINUTES      TOTAL_PRICE      Records      Min_Duration      Max_Duration
11793417      bob      -21201271.400000      -21237349.27      45283      -1277307175      29787

Brichsoft
..................
ACCOUNT_ID      ACCOUNT_NAME      TOTAL_MINUTES      TOTAL_PRICE
11793417      bob      8.866666      NULL

Bhavesh Shah

PROBLEM IS "TOTAL_MINUTES" Contain NULL Value.

thats why problem is coming.
so either you correct your data, or u can use IsNull() before doing SUM()

SUM(IsNull([dbo].[BILLING.PLAN].CALL_DURATION,0))

Qlemo

You have a very big negative value for Call_Duration - that one shown above (-1277307175). You should investigate why that value is there.

batman_k

ASKER

I found where the dormant record was residing.
Thanks for your support! Both ideas made my query far more precise.