.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Melody Anderson
Sharad
Eminent IT
Post New Web Links

Aggregate (count) function that produces a different result to what is expected.

Posted By:      Posted Date: November 29, 2010    Points: 0   Category :Sql Server
 
Hi Folks
I have an aggregate transformation, that runs a count on a column.  The  column  has 23 rows  with dates and a further 2 rows that have no date information. When I run a count function on the column, it returns a count of 25.  My hypothesis is that  this is linked to the 2 rows are treated as being  BLANK as opposed to being NULL (I understand the COUNT function will not count rows that have null values).

I have two questions
1) How do test this hypothesis that he difference is due to a blank vs null issue and then more importantly
2) How to I resolve the issue so that the 2 "empty" rows are not counted?

Many thanks

Steve




View Complete Post


More Related Resource Links

About the Aggregate Function MIN

  
Hi all, I am using the select statement with MAX(id), MIN(id), COUNT(*) of a very big table and it is returning me the value in less than a second. But surprisingly if i am using aggregate function MIN(id) in a seperate select statement it is taking upto 90 Seconds. Here are the 2 statements I am running SELECT MAX(ID),MIN(ID),COUNT(*) FROM A TABLE  -- Time consuming for this:  less than a Second. SELECT MIN(ID)FROM A TABLE  -- Time consuming for this:  Upto 90 Seconds. Please clarify how it work internally when we call the select statement with Aggregate functions?  chinna

Function count through Regular Expression

  
Hi, I have a table with varchar(max) column. This column stores all the function calls to application. Example 1    SUM(SALARY); AVG(LEAVES)/COUNT(DAYS); 2    SUM(SALARY)/AVG(SALARY); AVG(LEAVES)/COUNT(DAYS); COUNT(EMPLOYEES); The above output shows the requests of User 1 and 2. User 1 called SUM, AVG and COUNT functions once. User 2 called SUM once and AVG, Count twice. I need to calculate these counts through Regular expression in SQL Server 2005. Following is script for table and data generation: CREATE TABLE [dbo].[UserRequests] (  [UserID] [int] NOT NULL,  [Requests] [varchar](255) NULL ) ON [PRIMARY] insert into [dbo].[UserRequests] values (1, 'SUM(SALARY); AVG(LEAVES)/COUNT(DAYS);') insert into [dbo].[UserRequests] values (2, 'SUM(SALARY)/AVG(SALARY); AVG(LEAVES)/COUNT(DAYS); COUNT(EMPLOYEES);') Any suggestion or idea would be highly admired. PS: if someone has another optimized solution for this problem, kindly suggest that as well. Regards.

Function display char count

  
I want to know the meaning of function DisplayCharCount, span Counter ,documents.form[0],RegisterClientScriptBlock and Bolded line in the following programm and also let me know what is happening in this programm if(checkDisplayCount.Checked) {string scriptText="";scriptText+="function DisplayCharCount(){";scriptText+="spanCounter.innerText="+"document.forms[0].TextBox1.value.Length";scripttext+="}";ClientScript.RegisterClientScriptBlock(this.GetType(),"CounterScript",scriptText,true);TextBox1.Attributes.Add("onkeyup","DisplayCharCount()");LiteralControl("<span id=\"spanCounter\"></span>");PlaceHolder1.Controls.Add(spanLiteral);}}    

Please Provide the result in my Expected Format

  
Table:   Doc Sno A 1 B 2 C 3 Expected result is   Doc Sn1 Sn2 A 1 2 A 2 3 A 3 1 B 1 2 B 2 3 B 3 1 C 1 2 C 2 3 C 3 1 Ramesh.M

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

  

How do i get around this???

select

 

sum(PODet.UnitCost*ReceiverDet.Qty2Receive) as TotalCost

 <

Scope statement using Tail function has count error

  

My time hierarchy (TimeHier) has a QUADWEEK level with 28 members. When I select the last 13 members with the Tail function, I get the appropriate members:

Tail([Time].[TimeHier].[QUADWEEK].Members, 13))

However, if I use that same function inside a Scope statement, it only applies to the last 12 members. I have to increase the count by 1, to 14 to make it work inside the scope. I'm baffled.

Here's the full code:

CREATE

 

MEMBER CURRENTCUBE.[MEASURES].[YagoMemString] A

insert result of function in new table

  

Hi

is it possible to insert table result of Table_Valued function in the fly table ? or should i declare an table before?

Best. Morteza

 

insert * into #t from dbo.udfGet_Revenues(100)


max() agreate function and having clause not working as expected

  

I wil try to describe below my issue, starting with the query i use.


SELECT     b.[id_training_MD],
                   b.[id_training_Q_Log],
                   b.[trainingScore]
FROM [training_MasterData] as a
inner join [training_Log] as b
    on a.id_training_MD = b.id_training_MD
inner join [training_Q_Log] as c
    on b.id_training_Q_Log = c.id_training_Q_Log
WHERE (a.[Category] = 'Networking')  and  (c.[employeeID] = '123')

group by b.[id_training_Q_Log],b.[id_training_MD],b.[trainingScore]   
having b.[id_training_Q_Log] = (select distinct max(b.[id_training_Q_Log]) FROM [training_Log])


My tables are:

CREATE TABLE [dbo].[Training_MasterData](
    [id_Training_MD] [int] IDENTITY(1,1) NOT NULL,
    [Category] [varchar](50) NOT NULL,
    [TrainingName] [varchar](100) NOT NULL

Example of data from table:
[id_Training_MD]    [Category]         [TrainingName]
      3       

Writing a custom Aggregate function in SSIS

  

Hi is it possible to write a custom aggregate function in SSIS. Is there any other way to do it better? Any help is highly appreciated.

 

My values are like this

Item | VALUE

1,abc

2,def

1,xyz

1,wer

2,tyu

3,ppl

 

I am trying to bring the output. 

ITEM,VALUE

1, abc-xyz-wer

2, def-tyu

3, ppl


Ganesh Ranganathan
[Please mark the post as answer if it answers your question]
blog.ganeshzone.net

using an anonymous function for an action result

  

i can assign a simple anonymous function and it works, but i'm having trouble with a more complex one.

i am attempting to disable an image button after it is pressed.

any alternatives would be welcome, but i think this is the recommended

            <% var options = new AjaxOptions{ 
                  HttpMethod = "POST", 
                  UpdateTargetId = "lblAdded" + product.ProductId,
                   //OnComplete= "function() {alert('dddd');}",     <-- works
                  OnComplete = "function() {(document.getElementById('ibtn" + product.ProductId + "')).disabled='disabled';}" <-- does not work
                    }; 
            using (Ajax.BeginForm("AddToCart", new { id = product.ProductId }, options))
           { %>


 way to do it(?)


Cannot find either Column "dbo" or the user-defined function or aggregate "dbo.GetCandiateID", or th

  

Hi,

Good morning to everybody. I am not understanding why I am getting the following error (also mentioned in the subject line).

I have written the following function in SQL Server 2005:

CREATE FUNCTION dbo.GetCandateID()
RETURNS INT
AS
BEGIN
    declare @candidate_id int
    SELECT @candidate_id = max(c.candidate_id) from dbo.candidates c
    if(@candidate_id is null)
        set @candidate_id = 1001
    else
        set @candidate_id = @candidate_id + 1
    return @candidate_id
END

The function compiled properly. I have used the above function like below in the query:

select dbo.GetCandidateID()

I am getting the following error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetCandidateID", or the name is ambiguous


AGGREGATE over a time period: how to behave like a distinct count ? (aggregate on the full period an

  

I would like to compute a measure "MyMeasure" over the last 12 months :

Aggregate({[Event DATE].[Calendar].CurrentMember.Lag(11):[Event DATE].[Calendar].CurrentMember},[Measures].[MyMeasure])

MyMeasure is not a distinct count, so the results returned is the SUM of the distinct count performed on each month over the last 12 month.

What I would like to have is the distinct count over a full year (all months taken together).

For example, suppose that MyMeasure is a simple measure that always returns 1.

The aggregation returns 12 (1 for each month then SUM). I would like it to return 1 (measure on the whole period).

 
Can somebody help me that's really a very big issue I have to fix !

 
Thanks

can i create a Clr ud Aggregate Function with over/ order by clause?

  

i want create a UD Aggregate like  Row_Number () for using with 'Over (Order By ...) ' (not like sum() over (part....)

is it possible?
thanks.


SQL PROCEDURE cannot perform aggregate function on expression containing aggregate or subquery

  

Hi I want do SUM(TABLE1.PARAM1)*(SELECT TABLE2.PARAM FROM TABLE 2 WHERE TABLE2.PARAM3=TABLE.PARAM3

I DID BELLOW CODE BUT GETTING ERROR AS cannot perform aggregate function on expression containing aggregate or subquery

YOUR HELP REALLY APRECIATED

 

ALTER PROCEDURE ShowcClientvsTimevsCost(@Country nvarchar(15) )

AS
select  tTIMESHEET.ACTIVITY , sum(REV_BY_CLIENT.Jan+REV_BY_CLIENT.Feb+REV_BY_CLIENT.Mar+REV_BY_CLIENT.Apr+REV_BY_CLIENT.May+REV_BY_CLIENT.Jun+REV_BY_CLIENT.Jul+REV_BY_CLIENT.Aug+REV_BY_CLIENT.Sep+REV_BY_CLIENT.Oct+REV_BY_CLIENT.Nov+REV_BY_CLIENT.Dec) as SUMREVENUE ,
sum(tTIMESHEET.SUN+tTIMESHEET.MON+tTIMESHEET.THU+tTIMESHEET.WED+tTIMESHEET.THU+tTIMESHEET.FRI+tTIMESHEET.SAT)as SUMTIME ,
sum(((tTIMESHEET.SUN)+tTIMESHEET.MON+tTIMESHEET.THU+tTIMESHEET.WED+tTIMESHEET.THU+tTIMESHEET.FRI+tTIMESHEET.SAT)*
(SELECT HR_CHARGE  FROM tEMPLOYEE t  WHERE  t.EMP_ID =tTIMESHEET.EMP_ID ) )as SUMTIMECOST
from tTIMESHEET INNER JOIN REV_BY_CLIENT on
tTIMESHEET.ACTIVITY = REV_BY_CLIENT.Client and REV_BY_CLIENT.Country = @Country
group by ACTIVITY
RETURN 


Alternet Access Mapping is not producing the expected result

  

I have a web application at http://servername:portUmber. I want the site to be accessed through an address like: http://This.this.com.

in alternate mapping, I added an internal url > selected my collection > entered the http://This.this.com for "URL protocol, host and port. > changed the zone to interanet.

The site is working with port number but not with the alternate access mapped name.

rCentral Administration > Operations > Alternate Access Mappings  shows http://This.this.com for the internal URL and Public URL for zone in the

 

What am i missing?


Sara

Javascript Function Expected Error

  

I'm using the CustomValidation Function to call my javascript but for some reason I'm not getting the * to indicate the error to the user. I keep getting a Function expected Error, so what is wrong with my javascript that's causing this error and why aren't I receiving the * notification? Thank you in advance for your help.

Javascript:

<script type="text/javascript" language="javascript">
    function checkAGITotal()
    {
        
        var a = document.getElementById('<%= NumberInHouseholdTxtbox.clientid %>').value;
        var b = document.getElementById('<%= AGITotalTxtbox.clientid %>').value;


        if (a == "1" && b <= "27247" || a == "2" && b <= "35630" || a == "3" && b <= "44014" || a == "4" && b <= "52397" || a == "5" && b <= "60781" || +
        a == "6" && b <= "69164" || a == "7" && b <= "70736" || a == "8" &

SSRS aggregate function

  

I have an SSAS MDX query that uses a calculated member;  when adding the subtotal in the report I would like to rely on SSAS doing to aggregation rather than the incorrect sum SSRS is trying to do.  But when I use =Aggregate(Fields!x.Value)  I dont get anything back, the cell is blank.

Why?




Javier Guillen
http://www.msbicentral.com/Blogs/JavierGuillen.aspx
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend