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


Post New Web Links

About the Aggregate Function MIN

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
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


View Complete Post


More Related Resource Links

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

 <

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

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


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 


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

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

  
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


Userdefined aggregate function erroring out when all values are null

  

HI, I have a aggregate function that calculates median in all cases excet if every value that is being passed in is null and I cant seem on how to fix it. I beleive it has to do with either the read or write methods below is the code that I am using and the error. Any help on this would be greatly appreciated. Thanks in advane.

using

 

System;

using

 

System.IO;

<

Built-in Functions - Aggregate Functions

  
Aggregate functions return a single value summarizing a given data set. All aggregate functions are deterministic. NOTE: AVG, SUM, STDEV, STDEVP, VAR and VARP functions cannot operate on BIT data types; they can operate on all other numeric data types.

Content page control passes as argument to javascript function

  

Here is the content page:

********************************************************************

<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="JavaDynamicCopyValue.aspx.cs" Inherits="JavaDynamicCopyValue" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
 <script type="text/javascript" >
 function copyValue(input, output )
{
   ouput.value = input.value;
   return true;
}
 </script>
 <asp:TextBox ID="txt1" runat="server" onchange="copyValue(this,document.getElementById('<%=txt2.ClientID%>'))"></asp:TextBox>
    <br />
    <br />
    <asp:TextBox ID="txt2" runat="server"></asp:TextBox>
</asp:Content>

********************************************************************

I got a javascript run time error says output is undefined. if I view source of the page, the txt1 markup became:

<input name="ctl00$ContentPlaceHol

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly.

  

I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

 I have two SQL Server instances installed.

 1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?


Public Shared function doubt????

  
Hi
I am using VB.net/ASP.NET and SQL Server 2000 for a web application.

For populating the dropdowns, I wrote a Public Shared function LoadDDL(), in which I pass the dataset and dropdown name to set.

I have my doubts that this function will produce unexpected results for concurrent users. And will mix the results among sessions because it is a Shared function.

I am confused here. Is it the right way to go?

Please advice. Thanks in advance.

Pankaj

Trim function in C Sharp

  
Hi,
How do i use "trim" function in C Sharp? I have a string and i want to strip off spaces from the end of the string. Any idea how do i do it??

Thanks
may~

Pure C++: Invoking Generic or Function Templates

  

As I promised last time, in this month's column I'll walk through the process of defining and invoking a generic or template function under C++\CLI. A function template or a generic function begins with the template or generic keyword followed by its parameter list.

Stanley B. Lippman

MSDN Magazine October 2005


Cutting Edge: Using an Eval Function in Web Services

  

Web Services are often presented as the perfect tool for pro-grammers. They're interoperable, based on open standards such as SOAP and WSDL, and are fully integrated with the Microsoft® . NET platform.

Dino Esposito

MSDN Magazine September 2002


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