Coalesce In Aggregate Function

Feb 6, 2014

If you use Coalesce with Count will it return all values even when null? Reason I pose this question to you is that I am running the below query and am getting escalated results than what should be returned. 1st stop on the debug train is how does Coalesce handle it....for example Boston should return only 143 but the query retunrs 194 for Boston?

Code:
Select
Count(NumOnsite),
originatingCity,
Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames)) As PaperNames
From readytoshipOffsite
Group By originatingCity, Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames))

View 11 Replies


ADVERTISEMENT

Cannot Perform An Aggregate Function On An Expression Containing An Aggregate Or A Subquery.

Oct 19, 2007

Can any1 tell me why i am getting an error


SELECT DISTINCT

--p.voucher,

--p.amount,

p.siteID,

b.siteID,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers

FROM

BillingTotal b,

Payment p

--WHERE

-- s.sitename=@cmb1

--AND p.siteid = s.siteid

-- p.voucher = 0

-- p.voucher = 1

GROUP BY p.siteID,b.siteID



Msg 130, Level 15, State 1, Line 1

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

View 8 Replies View Related

COALESCE Function

Aug 7, 2001

Hi guys,

Do you have any idea why COALESCE function gives timeout in SQL2000 and works in SQL7.0

for ex.

in SQL7.0 this statement works fine in one of my SP

ROUND(COALESCE(ABS((SELECT SUM(Amount)))))

but in SQL2000 my SP is not working and my program gives timeout. But, if I change to ISNULL it works fine. Any clues on this issue?

This problem was there in SQL65 with ISNULL and then we have changed to COALESCE. Now, again repeated in 2000.

View 1 Replies View Related

Coalesce Function

Sep 8, 2004

hi,

my question is about using coalesce function in SQLServer. This function brings up the first not null value. my problem is i cannot get the corresponding field name. this is what i use

SELECT COALESCE(Stop1, Stop2, Stop3, ...., Stop10)
FROM ...
WHERE ProjectID = #URL.ProID#

it returns,

(no column name)
----------------
1 somebody@somthing.com

the fields in the DB goes like stop1, stop2, stop3,...
so, i need to get which stop i am .thanks in advance.

View 1 Replies View Related

Similar To COALESCE Function

Nov 4, 2004

I have three fields in a table say [F1, F2 & F3]. I need to fetch anyone of these three fields which has the maximum value between them.

In Simple words i'm looking for some function which is similar to COALESCE function which returns the first NOT NULL value of the fields that were passed as arguments.

FYI I'm using SQL Server 7.0 which does not supports UDF's

Earlier response appreciated

Thanks and Regards
Chandru

View 3 Replies View Related

Problem With COALESCE Function....

Feb 3, 2008

Hi all

If you look at BOL for [COALESCE] function we have:

Syntax
COALESCE ( expression [ ,...n ] )

Arguments
expression..is an expression of any type.

Return Types
Returns the same value as expression.

-------------------------------------------
Ok as it claimes , arguments can be of any type also the Return value, BUT when i execute the following command:

Print COALESCE('An String',12) -- just for example

and in generel when you have an string argument in this function you will receive the following error:

Syntax error converting the varchar value 'An String' to a column of data type int.

Could anyonel help me?
Thanks in advance.
Regards.

View 5 Replies View Related

COALESCE Function Problem

Sep 14, 2007

Hi, I downloaded "Microsoft SQL Server Compact Edition" v3.1, installed, created database in VS2005, created table TESTTABLE with one column TEST NVARCHAR 200 and run this command against it:

SELECT coalesce(test,NULL) from testtable

but it says

Error : The function is not recognized by SQL Server Compact Edition. [ Name of function = coalesce,Data type (if known) = ]

In this forums there are more usages of coalesce function, so I think it is supported and fully functional, but where is the problem, then ?

My system is Windows Vista Business (Czech version), version of sqlcese30.dll and sqlceqp30.dll is 3.0.5300.0.

thank you for suggestions


J.S.

View 13 Replies View Related

How To Use Coalesce Function In Derived Column Component?

Jan 19, 2007

Hi all,

You know the coalesce function is not offered in the derived column component. how can I use the coalesce function within the component? And how can I use a user defined function within derived column component? Would you be kind enough to give an example?

Thanks! Any reply or comment is highly appriciated.

Robert

View 5 Replies View Related

Aggregate Function

Feb 21, 2008

Does anyone know how to make a query and use an aggregate function?   This is my current code...any help would be great. 
"SELECT tblTopic.Topic_ID, tblTopic.Subject, MAX(tblThread.Message_date) AS MessageDate, tblThread.Message FROM (tblThread INNER JOIN tblTopic ON tblThread.Topic_ID = tblTopic.Topic_ID) WHERE (tblThread.Message_Date LIKE '%' + @fldGenus + '%' GROUP BY tblTopic.Topic_ID, tblTopic.Subject, tblThread.Message">
Also, How can i limit the query to only bringing up 5 records?
 I'm trying to get a datagrid to show the 5 most recent forum posts for a particular category. 
Thanks.

View 2 Replies View Related

Last Aggregate Function

Mar 9, 2007

Hello, I need to use last() aggregate function in MS SQL Server 2005 but it is not built in.How to replace this functionality?Thanks.

View 4 Replies View Related

Aggregate Function

Apr 3, 2007

I have three tables, tblschedule, tblresource and tblemployeename.
in tblschedule table there are scheduleID, resourceID and employeeID. In tblResource there are ResourceID and ResourceName. In tblemployeename there are EmployeeID, EmployeeFName and EmployeeLame. I want to have a report that show how many times the resource has been reserved by employee. i would like to have a report. Look like the following:

ResourceName EmployeeFName EmployeeLName (Or use EmployeeName) Number of record.

How to write a query? I use asp.net as front end.

Thank for your help.

View 1 Replies View Related

Aggregate Function MAX

Mar 4, 2004

Hi

I have a query where I need to use an aggregate function MAX in where clause, I know that MAX cannot be used in a Where clause because it is an aggregate function. Can anyone help me out in writing this query?


SELECT * FROM ACCOUNT_REVIEW AR INNER JOIN
QUESTION_RESPONSE ON
AR.Review_ID = QUESTION_RESPONSE.Review_ID
WHERE (MAX(AR.Review_Date) IS NULL)


I need it asap. Thanks in advance,


K

View 6 Replies View Related

Single Value From An Aggregate Function

Mar 19, 2006

Hey everyone, I'm looking for a way to insert a count of a set of records into a table and then read that table and produce an XML file from that dataset.  The problem that I'm coming across is that when you do this type of query you have to specify one of the values from the table and then get a count for the number of records of that particular range of values.
The table that I am trying to get the data from will look something like this:
ItemID   ModuleID   Description ......1              2869             blah blah blah2              2869             blah blah blah3              2869             blah blah blah4              2690             bit bit bit5              2690             bit bit bit6              2690             bit bit bit
So I count the 2869's and 2690's and place the ModuleID and the count of the ItemID's in another table.
Then I need to extract the data from the table and put it into an XML file that is readable by the ReadXML method in ASP.NET.
So far I've got the SPROC to do only one record at a time but I would like one that does the whole table rather than one at a time using a parameter.

View 5 Replies View Related

Aggregate Function Update

Mar 8, 2004

I am trying to write an update statement based on an aggregate and it will not let me. Please find below the SQL.

update abtimesummary
set hours = sum(a.hours)
from abtimestore a
join abtimesummary b
on (cast(a.weekno as varchar(10)))+'-'+(cast(a.empno as varchar(10))) = b.summaryid

and this is the error message:

Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.

Can someone tell me how to get round this please?

Many thanks

View 2 Replies View Related

Use Of Subqueries In An Aggregate Function

Jun 15, 2004

hi there,
I have a query that works on sybase and want to make it also works on SQL Server.
The problem is that in this query I 'm using a subquery in an aggregate function.
It seems that SQL Server unlike Sybase doesn't support the use of subquery in aggregate function.
How can I overcome this problem.

Thanks,
Fady

View 3 Replies View Related

Aggregate Function On SubQuery

Jan 20, 2006

I am working on a view in SQL Server 2005.
I am trying to get a list of the number of sessions each user had by user. I tried doing it this way, but


SELECT userid, MAX
((SELECT COUNT(DISTINCT sessionId) AS SESSIONCOUNT
FROM dbo.Sessions AS OD
HAVING (sessionId = O.sessionId))) AS MAXSESSION
FROM dbo.Sessions AS O
GROUP BY userid

but it throws an error 'Cannot perform an aggregate function on an expression containing an aggregate or subquery.'

Is there an elegant solution for this?

Thanks,

View 1 Replies View Related

Can I GROUP BY Aggregate Function (Like SUM)

Aug 7, 2007

Hello,

I column that calculated at run time in insert , can i gruop by this column,the new one that not exist yet

View 6 Replies View Related

How Would I Do This - Select With Aggregate Function

Apr 11, 2008

I have a table like this below:
<PRE>
Page Book Release MaxPages
1234ABC A1
1234ABC B2
9999ABC D1
9999ABC E2
7777ABC A1
7777ABC C2
</PRE>

I want to select every page of the book, but only the highest release of that page.

Something Like:

Select Book, Page, Max(Release), MaxPages
From Table


But I can't quiet figure it out.

Thanks for any help.

View 11 Replies View Related

OVER Clause With Aggregate Function

Jul 2, 2015

Why aren't we getting proper MAX(DOJ) value in Weird_Maxgroup Column in the below query ? Similar thing happens with MIN() function as well.

Is there something that I am missing here ?

;WITH CTE AS
(
SELECT 1 AS ID, 10000 AS SALARY,GETDATE() AS DOJ
UNION ALL
SELECT 1, 13000,GETDATE() - 10
UNION ALL
SELECT 3, 190000,GETDATE() + 10

[Code] ....

View 9 Replies View Related

Aggregate Function In SQL Statement.

May 6, 2008

Hello,

I want to count how many occurences their is of each date that is returned by my sql query. I am not sure how to add the aggregate function code to my query I know how to just tell it to count all records, but not to tell it to count for each group of dates. For example I want it to count how many times 5/6/08 shows up in the returned results and so on. Here is my query I currently have. Any help would be greatly appreciated! Thanks!

The enc_timestamp is my date field.

Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
From template_audit a
Join user_mstr b on a.created_by = b.user_id
GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name
Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
ORDER BY a.enc_timestamp, b.first_name, b.last_name;

Thanks in advance,
Sherri

View 17 Replies View Related

Product As Aggregate Function?

May 22, 2008

Hi guys,

Does anybody know a convenient way to return the product of a column, similar to something like the 'sum' function?

If I have a table of form id-date-value, I'd like to do something like:

select id, date, product(val)

If not, does anyone know of a way one could structure this as a subquery?


If all else fails, I suppose I'll have to resort to a loop or cursor, but I'd rather not.

Any help would be much appreciated.

View 11 Replies View Related

Aggregate Function In Sub-query

Jan 15, 2014

I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.

SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode
FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID
WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);

My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.

View 3 Replies View Related

Group By By Using Aggregate Function

Dec 23, 2014

I have a query that I need to group by by using the aggregate function using MSSQL...

I want to be able to group the following columns into ONE line:

AddedDescription, OurRef, MaterialName, StockFamily, DateCreated, WJCStatusID AND THEN have WeightToSend Summed for all lines that are grouped above.

View 2 Replies View Related

Problem About 'aggregate Function' Please Help

Mar 18, 2006

Hi,
I just started database class
Today is the first day i'm writtng code in sql
SO when i try to run this code

SELECT CUSTOMER.CUS_CODE,CUS_BALANCE, SUM(LINE_UNITS*LINE_PRICE) AS TotalPurchases
FROM CUSTOMER, INVOICE, LINE
WHERE CUSTOMER.CUS_CODE=INVOICE.CUS_CODE AND INVOICE.INV_NUMBER=LINE.INV_NUMBER
GROUP BY CUSTOMER.CUS_CODE;

It says "You tried to execute quiry that does not include the specified expression 'CUS BALANCE' as a part of aggregate function.

Please help me to fix it.

View 4 Replies View Related

Aggregate Function Error

May 6, 2006

I have made an aggregate function sql (as far as I understand).I need the sum but also the features column. But I can only read one column in the select.Here is the select trying to read two columns:

select oitems.catalogid,oitems.features, sum(oitems.numitems) as SumOfItems from oitems

here is the error:


[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'features' as part of an aggregate function.

...anyone?

View 3 Replies View Related

Sum Aggregate Function Problem

Jun 30, 2007

Hi,

I've the following query:
select F_Mode, Issue_Count, Sum(Issue_Count) as IssueSum, milestone from rpt_fact_Paretos
Group By F_Mode having milestone = 'SDS'

Sample Data:
F_Mode Issue_Count milestone
Fail1 5 SDS
Fail2 6 SDS

If the above query is executed, the resultset is:
Fail1 5 5 SDS
Fail2 6 6 SDS

Question: I'm not getting the SUM of Issue_Count. I agree that I'm using F_Mode as Group By and hence the result. But if I use milestone as Group By I won't get all the F_Mode in the result set.

I'm looking for a result like this:

Fail1 5 11 SDS
Fail2 6 11 SDS

How can I achievce this?

Thanks in advance,
SD

View 2 Replies View Related

Aggregate Function/ORDER BY

Mar 21, 2008

SELECT [LastNameOfProfessor], avg(ProfessorRating) AS [Average] FROM [Table1] GROUP BY [LastNameOfProfessor]

How would I order this by [Average]?

View 6 Replies View Related

Aggregate Function Trouble

Nov 20, 2007

Problem 1:


Suppose I have a table

T( A, B, C, D, E )

I want to retrieve D, E for all rows which have minimum C when we do a group by on A and B. The query would look something like

SELECT D, E FROM T HAVING C = MIN( C ) GROUP BY A, B

Unfortunately, SQL server gives the error that D, E are not in a group by clause. One way is:

SELECT T.D, T.E
FROM
T,
(
SELECT A, B, MIN( C ) AS F FROM T GROUP BY A, B
) AS T1
WHERE
T.A = T1.A AND
T.B = T1.B AND
T.C = T1.F


What I want to know is whether there is a simpler way of doing this or not, kinda like the first illegal query.

Problem 2:

Suppose in the query

SELECT A, B, CASE WHEN C = 'A' THEN 1
WHEN C = 'B' THEN 2 END ORDER

I want to do MIN( ORDER ). The simple way would be

SELECT A, B, MIN( CASE WHEN C = 'A' THEN 1
WHEN C = 'B'' THEN 2 END )

But this gives an error. There is a way:

SELECT T.A, T.B, MIN( T.C ) FROM
(
SELECT A, B, CASE WHEN C = 'A' THEN 1
WHEN C = 'B'' THEN 2 END AS C
) AS T


but I want to know if there is a simpler way to do this (kinda like the first illegal query).

Regards,

id

View 13 Replies View Related

Custom Aggregate Function

Apr 4, 2006

hi how do i write a custom aggregate function or select query.I have Table name MemberDetail where which has two column Memberid and WokingDay.In this table same member could have multiple working days. i want select query that will return single row for each member and his/her quama separated working days. without using cursor. how do i do that.

eg

Table MemberDetail

MemberID WorkingDay

M1234 12

M1234 10

M1555 02

M1555 15







View 5 Replies View Related

Boolean Aggregate Function

Mar 2, 2008

I don't know if this is the right forum.

I have a MS ACCESS table like this:

ID - Col1 - Col2
Number - Yes/No - Yes/No
=====================
1 - Yes - No
1 - No - No
2 - No - No
2 - Yes - No
2 - Yes - Yes

I would like to create a view that groups BY ID and combines the boolean columns into 1 value using OR:

ID - Col1 - Col2
=====================
1 - Yes - No
2 - Yes - Yes

Is there an aggregate function for this in T-SQL? If not, how can I solve this?

View 7 Replies View Related

How To Pass Parameter In Max Aggregate Function

May 20, 2008

i have on table temp and it has column name fhID, now that column name and table name comes as parameter in stored procedure from c#.
now stored procedure has code somthing like below
@column nvarchar(50)
@tbname nvarchar(50) 
            -- both are parameter comes from c#
decalre @maxid
select   @maxid=max(@column) from @tbname
          rather than prints the maximum values of "fhid" it prints column name itself.
now i how can i achive actual working of max function using parameter.
is there any other way to pass parameter in aggregate function or it is not allowed to pass.???
plz help me,
thanks
vishal parekh
          'fhid'
now how can

View 7 Replies View Related

Aggregate Function/Join Troubles

Nov 28, 2007

Hey,

I am running into trouble performing a second join on a column while using aggregate functions over that table.

What I think is happening is that the aggregate values (sum, avg, count) are being multiplied because I am joining on the same table a second time, but I don't know how to fix or avoid this problem.

Here's the query:

---

SELECT
CAST(ro.xing_tc_no AS INT) AS loc_id,
SUM(ro.total_fatal) AS tot_fatal,
SUM(ro.total_serious) AS tot_serious,
SUM(ro.total_minor) AS tot_minor,
COUNT(ro.report_no) AS tot_accidents,
AVG(ro.NO_OCCUPANTS) AS num_occupants

FROM
UserHotspots uh

LEFT JOIN rods_k_crossing AS rkc
ON rkc.location_id = uh.CrossingID

LEFT JOIN rods_occurrence AS ro
ON rkc.tc_location_nbr = ro.xing_tc_no

GROUP BY
ro.xing_tc_no

---

The second join is also performed on rods_k_crossing and that is when the sums, avgs, and counts get multiplied. If I take that second join out the numbers come out fine.

Thanks for any help!

View 2 Replies View Related

Aggregate Function - Tough Query For Me...

Oct 17, 2006

I currently have the following query:

Quote:
select distinct a.memberFirstName, a.memberLastName, c.ChapterName, d.divisionName,
count(f.memberID) as numMembers
FROM Members a
INNER JOIN groupLeaders b
ON a.memberID = b.memberID
Inner JOIN Chapters c
ON c.chapterID = b.chapterID
LEFT JOIN divisions d
ON d.divisionID = c.divisionID
Inner Join groupsOfEight e
ON e.groupLeaderID = b.groupLeaderID
Inner Join groupOfEightMembers f
ON f.groupOfEightID = e.groupOfEightID
Group BY a.memberFirstName, a.memberLastName, c.chapterName, d.divisionName
Order By divisionName, numMembers

This query returns me the names of all of my Group Leaders, their Chapter, Division, and the number of members they have selected to be in their group.

Now, instead of the number of members in each Group I would like to know the total number of Members in each division to appear in the count.

[NOTE: All chapters have a division, linked by a divisionID in the "Chapters" table -- I need to get a count of all the "ChapterMembers" [chaptermembers is a table also] that are in the Division.

Here is the query I started to build before I ran into serious trouble:

Quote:
select a.divisionName, count('c.memberID') as numMembers
From Divisions a
Inner Join Chapters b
On b.divisionID = a.divisionID
Inner Join chapterMembers c
ON c.chapterID = b.chapterID
Left Join Members d
ON d.memberID = c.memberID
LEFT Join groupLeaders e
On e.memberID = d.memberID
Group By a.divisionName

This particular query returns only the DivisonName and the number of Members in the division as expected. However, when I try to select the information for the GroupLeader (first & last name) I am forced to add memberFirstName to the Group By statement which changes my Count...

Have I done an okay job of explaining the problem?

The goal here is to select all of the GroupLeaders first & last name, their chapterName, divisionName, and the total number of members in the division.

Thanks for any advice!

Zoop

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved