Sending Column Name As A Parameter To Aggregate Function
Jul 29, 2006
How can do this. Because my stored function contains same clause except colums name.So I want to use column name as a parameter but how can send column name and use it like Sum(parameter) function .
my procedure like this:
ALTER PROCEDURE [dbo].[ORNEK10]
@YIL VARCHAR(4),
@TEKLIF_TURU VARCHAR(255)
AS
BEGIN
DECLARE @N1 FLOAT
DECLARE @N2 FLOAT
SET @N1 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N1')
SET @N2 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N2')
SET NOCOUNT ON;
--I want to avoid using if statements for Sum() function
IF(@TEKLIF_TURU='BASKAN_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(BASKAN_TEKLIF) AS YILI,
((100+@N1)*SUM(BASKAN_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(BASKAN_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
IF(@TEKLIF_TURU='ENCUMEN_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(ENCUMEN_TEKLIF) AS YILI,
((100+@N1)*SUM(ENCUMEN_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(ENCUMEN_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
IF(@TEKLIF_TURU='MECLIS_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(MECLIS_TEKLIF) AS YILI,
((100+@N1)*SUM(MECLIS_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(MECLIS_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
END
View 4 Replies
ADVERTISEMENT
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
Jul 9, 2007
In the provided CLR-function sample there is only one parameter from the SQL statement to the function. I can't find any info about how to pass more then one parameter. Is this possible somehow?
like:
Code Snippet
Select ShopCode,PickAValue(OrderDate,Article) as BestArticle
from T_Sales
Group by ShopCode
hans
View 4 Replies
View Related
Jul 20, 2005
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
View 5 Replies
View Related
May 6, 2015
I am trying to create a column chart that calculates the percentage of computers in our IT environment that are Actively communicating to our SCCM Server.
I have two datasets:
1. Total_Count_Of_AD_PC DataSet.
2. PC_With_Active_SCCM_Clients dataset.
Basically i wan to calculate the percentage for each Region (i.e. AP for Asia Pacific, EMEA, Americas).
Below is the Total_Count_Of_AD_PC Dataset screenshot.
Below is the PC_With_Active_SCCM_Clients dataset.
Below is the expression that i used that is causing the error.
=CountDistinct(IIf(Fields!Region.Value="AP", "PC_With_Active_SCCM_Clients"),(Fields!Name.Value, "PC_With_Active_SCCM_Clients"),Nothing)/CountDistinct(IIf(Fields!Region.Value="AP", "Total_Count_Of_AD_PC"),(Fields!name.Value,
"Total_Count_Of_AD_PC"),Nothing)
Below is the error message....
The Y expression for the chart ‘Chart2’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.
View 6 Replies
View Related
May 4, 2006
Hello i have this query and I havent been able to solve it
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FN_NIVELCOMPETENCIACARGO", or the name is ambiguous.
I have tried with dbo and withou dbo, specifyng columns inside it,
Thanks
select a.nmempresa,a.nmtipocompetencia, d.dsnombreciclo, c.dsnombrecargo,a.dstipocompetencia, a.dscatalogo,
b.popeso as PesoTipoCompetencia, f.dsnombrecompetencia as Competencia, e.pocompetencia as PesoCompetencia,
g.DSOPCIONESCALA, g.PESO
from
tgsc_tiposcompetencias a, TGSC_TIPOSCOMPETENCIASxcargo b, tgsc_cargos c, tgsc_ciclos d,
tgsc_competenciasxcargo e, tgsc_competencias f,
(select
dbo.FN_NIVELCOMPETENCIACARGO(a.nmempresa,a.nmciclo, a.nmtipocompetencia,c.nmcargo,f.nmcompetencia)) as g
where a.nmempresa=72 and a.nmciclo=9
and b.nmtipocompetencia=a.nmtipocompetencia
and b.nmcargo=10 and b.nmempresa=72 and b.nmciclo=9
and c.nmcargo=10 and c.nmempresa=72 and c.nmciclo=9 and d.nmciclo=9
and e.nmcargo=10 and e.nmciclo=9 and e.nmempresa=72
and f.nmcompetencia=e.nmcompetencia and f.nmtipocompetencia=a.nmtipocompetencia
and f.nmtipocompetencia=b.nmtipocompetencia
View 7 Replies
View Related
Apr 3, 2015
I have a specific variation on the standard 'Column Invalid' question: I have this query that works fine:
SELECT vd.Question ,
csq.Q# ,
csq.Q_Sort ,
csq.Q_SubSort ,
AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
vd.RType
[Code] ....
When I add this second average column like this:
SELECT vd.Question ,
csq.Q# ,
csq.Q_Sort ,
csq.Q_SubSort ,
AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
[Code] ....
I get the error: Column 'dbo.vwData.Response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Clearly things are in the right place before the change, so I can only assume that the OVER clause is my problem. Is this just not possible?
View 1 Replies
View Related
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
May 16, 2012
I am working on migrating view from Ms Access to SQL server. I got a query and modified it by removing IIF by CASE WHEN. I landed into following query:
Code:
SELECT CASE WHEN <CONDITION>
THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE))
ELSE 0
END AS CurrentDateAdj,
Year(CurrentDateAdj) + '_' + 'some text and processing')
FROM INCREMENTDATATABLE;
Here DAY1 and YR1 are from INCREMENTDATATABLE.
I am getting error that CurrentDateAdj not found. How can I fix this?
View 4 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
Apr 3, 2007
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
View 3 Replies
View Related