[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Null value is eliminated by an aggregate or other SET operation

Posted on 2010-08-16
9
Medium Priority
?
462 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:batman_k
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 71

Accepted Solution

by:
Qlemo earned 1600 total points
ID: 33446244
The warning is coming from NULL values in either or both of the SUMmed columns CALL.DURATION and TOTAL_PRICE_PER_CALL. Either that is correct or a warning of inappropriate data.

Please try the following for test:

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,
count(*) as Records,
min([dbo].[BILLING.PLAN].CALL_DURATION) as Min_Duration,
max([dbo].[BILLING.PLAN].CALL_DURATION) as Max_Duration
FROM [dbo].[BILLING.PLAN]
GROUP BY [dbo].[BILLING.PLAN].ACCOUNT_ID, [dbo].[BILLING.PLAN].ACCOUNT_NAME

and post the result for above record.
0
 
LVL 2

Author Comment

by:batman_k
ID: 33446409
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.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 33446420
That is thought to be used without the INSERT, only the SELECT (as shown). We don't want the values to be inserted yet.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 400 total points
ID: 33446541
Problem is one of the value coming as NULL, so you getting that warning message.
- this is just warning message,it wont stop your execution.

to debug that Firstly fire below 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]
WHERE [dbo].[BILLING.PLAN].CALL_DURATION IS NULL OR [dbo].[BILLING.PLAN].TOTAL_PRICE_PER_CALL IS NULL
GROUP BY [dbo].[BILLING.PLAN].ACCOUNT_ID, [dbo].[BILLING.PLAN].ACCOUNT_NAME


so you will get the data with NULL values.



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

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

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33446549

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



0
 
LVL 2

Author Comment

by:batman_k
ID: 33446722
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

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33446981
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))
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 33447160
You have a very big negative value for Call_Duration - that one shown above (-1277307175). You should investigate why that value is there.
0
 
LVL 2

Author Closing Comment

by:batman_k
ID: 33447800
I found where the dormant record was residing.
Thanks for your support! Both ideas made my query far more precise.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question