Hi everyone,
I have a database with the following tables:
- programmer
- software
- studies(name, splace, cost, course)
I would like to find out which school has the max. nr. of students, but my query - SELECT splace FROM studies HAVING count(name) = (SELECT max(count(name)) FROM studies) - doesn't works, becouse MSSQL doesn't supports nested aggregat functions.
What should I do? Please help me, thanks in advance.
I'm using the data environment of VB 6.0 to create data reports.
In Oracle you can use a nested aggregate function in a select query like MAX(SUM(Field)). Can I do this in SQL? If so, how?
thanx
I have a table that is used for employee evaluations. There are six questions that are scored either 1, 2, 3, 4, or 5. I want to tally the responses on a page, but I wonder if I can do it without 35 separate calls to the database (I also want to get the average response for each question). I know I can do "SELECT COUNT(intWorkQuality) AS Qual1 FROM dbo.Summer_Project_Req WHERE intWorkQuality = '1' " and then "SELECT COUNT(intWorkQuality) AS Qual2 FROM dbo.Summer_Project_Req WHERE intWorkQuality = '2' " and so on. But can I somehow do the aggregating at the page level, and just refer back to a datasource that uses a generic statement like "SELECT intWorkQuality, intDepend, intAnalyze, intWrite, intOral, intCompatibility FROM dbo.Summer_Project_Req"? If I can, I am not sure what type of control would be best to use or what syntax to use to write the code-behind. I would like the results to be displayed in a grid format. Thanks in advance for your help.
View 3 Replies View RelatedCan i combine two aggregate functions in one select statement?Like sum(count (field 1) * field 2) as tcost.Can i do something like that in my query?
thanks!
Funnyfrog
Hi..
I'm doing a jsp with database using sql.. I'm trying to get the max of score group by name from this table:
Table record
id_no. . . .name. . . . .score
. . 1 . . . . .Philip . . . . .10
. . 2 . . . . . Jane . . . . .12
. . 3 . . . . . John . . . . .15
. . 4 . . . . . Anna . . . . .11
. . 5 . . . . . Jane . . . . .16
I use the code:
query = "select name,max(score) from record group by name";
resultset = stmt.executeQuery (query);
However, there is an error. It says, SQL Exception: Column not found
I've checked several tutorial sites and the codes there are the same command that I use. I've also tried using other aggregate functions like the sum, still the same error comes out..
Why is that so? What should I do?
Thanks a lot..
I'm trying to migrate an app. from Access to SQL Server, and find that Transact-SQL does not support LAST/FIRST functions. Is there any alternative to these?
Below is the Access SQL statement:
SELECT Last(tblZoneNameString.Val) AS strZoneName, tblZoneNameString.TagIndex
FROM tblZoneNameString
GROUP BY tblZoneNameString.TagIndex
HAVING (((tblZoneNameString.TagIndex)>0));
I have a the following fields in a table TblToDo
[ToDoID] [int] IDENTITY(1,1)
[ToDoReferralID] [int]
[ToDoDate] [datetime]
[ToDoCompleteBy] [datetime]
[ToDoCompleted] [datetime]
and want to produce a query to show the with following
All Grouped by [ToDoReferralID]
Count [ToDoID] AS [ToDoToTal]
Count [ToDoID]WHERE [ToDoCompleted] IS NULL AS [ToDoIncomplete]
Count [ToDoID]WHERE [ToDoCompleted] IS NULL AND [ToDoCompleteBy] <= GETDATE() AS [IncompleteUTarget]
Count [ToDoID]WHERE [ToDoCompleted] IS NULL AND [ToDoCompleteBy] > GETDATE()AS [IncompleteOTarget]
I can manage a single aggregate but where to being trying to incorporate all into one query.
Something a little different: C# on SQLTeam!
It's basically just cobbled together using the aggregate function and TVF in the StringUtilities sample as a starting point.
Er, sorry MS, I forgot to change the namespace from Microsoft.Samples.SqlServer.
/*
Aggregate functions: UnionBinary, IntersectBinary
Table-valued function: SplitBinary
*/
using System;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Microsoft.SqlServer.Server;
[assembly: System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design",
"CA1020:AvoidNamespacesWithFewTypes", Scope = "namespace",
Target = "Microsoft.Samples.SqlServer")]
namespace Microsoft.Samples.SqlServer
{
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Microsoft.SqlServer.Server.Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = true,
IsInvariantToOrder = true,
MaxByteSize = 4100)
]
public class UnionBinary : Microsoft.SqlServer.Server.IBinarySerialize
{
private byte[] bitset;
public void Init() { bitset = new byte[4]; }
public void Accumulate(SqlInt16 value)
{
if (!value.IsNull && value.Value >= 0) {
if (bitset.Length <= value.Value / 8) {
Array.Resize(ref bitset, (value.Value/32 + 1) * 4);
}
bitset[value.Value / 8] |=
System.Convert.ToByte(1 << (value.Value % 8));
}
}
public void Merge(UnionBinary other)
{
if (other.bitset.Length > bitset.Length) {
Array.Resize(ref bitset, other.bitset.Length);
}
for (int i = 0; i < bitset.Length; i++) {
bitset[i] |= other.bitset[i];
}
}
public SqlBinary Terminate() { return new SqlBinary(bitset); }
public void Read(BinaryReader r)
{
int i = r.ReadInt32();
bitset = r.ReadBytes(i);
}
public void Write(BinaryWriter w) {
w.Write(bitset.Length);
w.Write(bitset);
}
}
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Microsoft.SqlServer.Server.Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = true,
IsInvariantToOrder = true,
MaxByteSize = 4100)
]
public class IntersectBinary : Microsoft.SqlServer.Server.IBinarySerialize
{
private byte[] bitset;
public void Init() { bitset = new byte[4]; }
public void Accumulate(SqlInt16 value)
{
if (!value.IsNull && value.Value >= 0) {
if (bitset.Length <= value.Value / 8) {
Array.Resize(ref bitset, (value.Value/32 + 1) * 4);
}
bitset[value.Value / 8] &=
System.Convert.ToByte(1 << (value.Value % 8));
}
}
public void Merge(IntersectBinary other)
{
if (other.bitset.Length > bitset.Length) {
Array.Resize(ref bitset, other.bitset.Length);
}
for (int i = 0; i < bitset.Length; i++) {
bitset[i] &= other.bitset[i];
}
}
public SqlBinary Terminate() { return new SqlBinary(bitset); }
public void Read(BinaryReader r)
{
int i = r.ReadInt32();
bitset = r.ReadBytes(i);
}
public void Write(BinaryWriter w) {
w.Write(bitset.Length);
w.Write(bitset);
}
}
public sealed class SplitBinaryCls
{
[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable SplitBinary(SqlBinary argument)
{
List<Int16> r = new List<Int16>();
if (!argument.IsNull) {
for (int i = 0; i < argument.Length; i++) {
for (int j = 0; j < 8; j++) {
if ((argument.Value[i] & Convert.ToByte(1 << j)) != 0) {
r.Add(Convert.ToInt16(i*8+j));
}
}
}
}
return r;
}
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design",
"CA1021:AvoidOutParameters")]
public static void FillRow(Object obj, out Int16 stringElement)
{
stringElement = (Int16)obj;
}
private SplitBinaryCls() { }
}
}
To install the compiled dll something like:
CREATE ASSEMBLY BinaryUtilities
FROM 'C:...BinaryUtilitiesindebugBinaryUtilities.dll'
WITH permission_set=Safe;
GO
CREATE AGGREGATE UnionBinary(@input smallint)
RETURNS varbinary(4096)
EXTERNAL NAME [BinaryUtilities].[Microsoft.Samples.SqlServer.UnionBinary];
GO
CREATE AGGREGATE IntersectBinary(@input smallint)
RETURNS varbinary(4096)
EXTERNAL NAME [BinaryUtilities].[Microsoft.Samples.SqlServer.IntersectBinary];
GO
CREATE FUNCTION SplitBinary(@input varbinary(4096))
RETURNS TABLE(ix smallint)
AS EXTERNAL NAME [BinaryUtilities].[Microsoft.Samples.SqlServer.SplitBinaryCls].[SplitBinary];
GO
I have a select statement like this:
"SELECT AVG(Price) FROM prices GROUP BY Category"
Which of course returns a list of average prices.
I want to get a sum of that list, something that would work like this:
"SELECT SUM(SELECT AVG(Price) FROM prices GROUP BY Category)"
That code doesn't work, but that's what I want it to do and I'm not sure how to do it.
Thanks!
Hi.Just as we have AVG(COLUMN_NAME) and MAX(COLUMN_NAME) how can I writemy own variation of a such a function. If I can appreciate how to dothis,then I hopefully I can write a MEDIAN(COLUMN_NAME) type function or amore general function like a 10% percentile function with syntax suchasPERCENTILE(COLUMN_NAME,25).Regards JC......
View 2 Replies View RelatedHi allFirstly this my first time posting to technical groups - so anymistakes I apologise for in advance.I am trying to count records in several secondary tables for the samerun in a primary table. However, there might be no records in thesesecondary tables for the specific run. Hence the sql below returnsnulls.Select run, (select count(errors) from table2 where run = t1.run groupby run) as errors, (select count(user) as users from table3 where run =t1.run and user = active group by run, dd)from table1 t1(Please note the different group bys. )I do not want nulls to be returned but to be replaced with 0. I havetried the isnull function but this does not work. egSelect run, (select isNull(count(errors),0) from table2 where run =t1.run group by run) as errors, (select isNull(count(user),0) as usersfrom table3 where run = t1.run and user = active group by run, user)from table1 t1Nor will isnull work if I put it around the select clause.Any suggestions?Thanks for the help!
View 4 Replies View RelatedDo aggregate functions (sum, count, min, max) inherently cause tablelocks?More concretely, would the following query typically result in a tablelock?select sum(quantity) as totalfrom productsThanks,Scott
View 3 Replies View RelatedI have a complex statement that is used in a SELECT statement. Afterall my calculations I need to do an additional filter on the results.Can I use an alias anywhere or is the AS clause just for columndisplay purposes?Instead of:SELECT column1,column2,some_complex_statement_using_SUM_AVG_and_a_few_CAS E_statementsAS TotalFROM table_nameHAVINGsome_complex_statement_using_SUM_AVG_and_a_few_CAS E_statements[color=blue]> 1[/color]Can I have:SELECT column1,column2,some_complex_statement_using_SUM_AVG_and_a_few_CAS E_statementsAS TotalFROM table_nameHAVINGTotal > 1As I understand it, the HAVING clause is used for filtering AFTERaggregate functions are calculated so the alias SHOULD be available.
View 1 Replies View RelatedI have Following SQL query
SELECT SUM(TOTAL) FROM MYTABLE WHERE DATEORIGINATED='11/11/07'
the above query will give me total for dateoriginated , but I am reading that dateoriginated value from another table and that table has multiple value of dateoriginated.
what I would like to do is to run above SQL aggregate functions query for two different dates at the same time and I would like to get total
help me how to do this?
thank u
maxmax
Hello Experts,
Currently my report is being shown like this:
Child Cards
K04-EP-MS48S (4/12)X
1
3
5
2
K12-EP-MS128(0/0)-XX
1
0
I would prefer the report as
Child Cards
K04-EP-MS48S (4/12)X
6
5
K12-EP-MS128(0/0)-XX
1
0
Is there any way that this is possible?
Thanks.
Lakshman.
Hi, need help in this statement here. I have three tables here, i.e. Sales, SalesItem, & SalesPmt. I want to display a grid that shows the Total Bill and Total Payment amounts.
My try is like this: SELECT SalesNo, SUM(Price*Qty) AS TotalBill, SUM(Payment) AS TotalPayment FROM ... GROUP BY....
No syntax error or whatever found, but the result of the total amounts is incorrect.
Say the data of the respective table below:
SalesItem
No
Qty
Price
1
1
5.00
2
2
12.00
3
4
3.50
SalesPayment
No
Amount
1
10.00
2
5.00
But the result I get from the above query is:
TotalBill
TotalPayment
86.00
45.00
Total Bill should be 43.00 and Total Payment should be 15.00.
Apparently the problem is due to the fact that I and querying on multiple tables. The correct total payment amount was multiplied by the number of rows of sales items (15.00 x 3), while the correct total bill amount was multiplied by the number of rows of sale payments (43.00 x 2).
So, what is the better way of writing this query?
Hi there,
I'm probably just being thick but is there a way to use conditional statements within aggregate functions? I'm trying to do something along the lines of the following -
Code:
SELECT SUM(
CASE
WHEN Currency='GBP' THEN TotalAmountCharged
ELSE TotalAmountCharged/1.45
) as total
from bookshop_orders
where year(OrderDate) = 2004 and month(OrderDate) = 9
TIA
Nick
Howdy,I need to write an update query with multiple aggregate functions.Here is an example:UPDATE tSETt.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ),t.b = ( select sum(f.q) from dbo.foo f where f.p = t.y )FROM dbo.test tBasically I need to get some aggregate statistics about the rows offoo and store them in rows of t. The above statement works fine...butnote how the two subSelect's have the exact same WHERE clause. Thisscreams at me to combine them...but how? I would like to havesomething like this in my query:SELECT avg(f.q), sum(f.q) FROM dbo.foo f WHERE f.p = 2...and somehow store the results in t.a and t.b. Is there any way todo this?Thanks before hand!
View 6 Replies View RelatedHi Guys,
I am having trouble with a particular query that is beyond my scope of understanding.
Basically I need to pull sales records based on the following criteria:
I have CustomerID, InvoiceNumber, ContractEndDate, MobileNumber, etc..
Customers recontract their mobile phone plans through us, and we have a new sales record for each time they recontract.
For example, CustomerNumber 123 has recontracted 3 times..
once on 2006-01-01, then on 2007-02-12, and finally on 2008-02-15..
So they have a 12 month contract each time.. then come in to recontract it.
So.. a customer has a single Customer Detail record, but may have many sales records attached. And a customer may have several sales for the SAME mobile phone number.
Currently to pull ALL sales records for all customers, my query is this:
Code:
SELECT xxx.CustomerID AS xxx_CustomerID,
xxx.Invoice AS xxx_Invoice,
yyy.PhoneType AS yyy_PhoneType,
yyy.PlanType AS yyy_PlanType,
yyy.ContractEnds AS yyy_ContractEnds,
yyy.MOB AS yyy_MobileNumber
FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice
WHERE yyy.ContractEnds IS NOT NULL
AND xxx.CustomerID IS NOT NULL
We want to get a list of customers that we can call to recontract, based on the ContractEnd field.
However, we want UNIQUE mobile phone numbers, with the LATEST ContrtactEnd date.
So, Customer 123 has 6 sales, for 2 unique Mobile numbers, the sql may be like:
Code:
SELECT MAX(yyy.ContractEnds) AS LatestCED, yyy.MOB
FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice
WHERE xxx.CustomerID='123'
GROUP BY yyy.MOB
Now, this works fine, and of course if i remove the WHERE clause, it collects all unique mobiles, with latest ContractEnd date for each, for all customers. (Customer 123 displays 2 mobile numbers, each with the LATEST ContractEnd date)
BUT i need this information ALONG WITH the other fields (xxx.CustomerID, xxx.Invoice, yyy.PhoneType, yyy.PlanType) and i have tried a few ways of doing it, but can't get my head around it..
Keep getting errors about Aggregate functions and Group By clause, and i understand why i am getting them, just cant think of any alternative query.
Can anyone please help me!
Thanks guys,
Mick
Hi all!In a statement I want to find the IDENTITY-column value for a row thathas the smallest value. I have tried this, but for the result i alsowant to know the row_id for each. Can this be solved in a neat way,without using temporary tables?CREATE TABLE some_table(row_id INTEGERNOT NULLIDENTITY(1,1)PRIMARY KEY,row_value integer,row_name varchar(30))GO/* DROP TABLE some_table */insert into some_table (row_name, row_value) VALUES ('Alice', 0)insert into some_table (row_name, row_value) VALUES ('Alice', 1)insert into some_table (row_name, row_value) VALUES ('Alice', 2)insert into some_table (row_name, row_value) VALUES ('Alice', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 2)insert into some_table (row_name, row_value) VALUES ('Bob', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 4)insert into some_table (row_name, row_value) VALUES ('Celine', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 6)select min(row_value), row_name from some_table group by row_name
View 2 Replies View RelatedHow to write Aggregate functions for tables and lists as If I can write them many problems in my reports will be solved. I tried writng it in the filters but I got an error saying Aggregate functions are not allowed for tables and lists. Can any one help me in this regard?????
View 4 Replies View RelatedI have a simple query like:
select max(PTR_DATE)
from MPR_portfolio_transactions
group by PTR_SYMBOL
and this is working fine, but if I add an extra column with another field like:
select max(PTR_DATE) , PTR_SHAREBALANCE
from MPR_portfolio_transactions
group by PTR_SYMBOL
Then I get an error message like:
Column 'MPR_portfolio_transactions.PTR_SHAREBALANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
How can I get round to this?
Hi,
Assume there is no sale with over 2000 units, the first query returns no row while the latter returns 1 row with a null value. I am not questioning the result, I would like to understand conceptually what semantic difference between the Sql99 analytic function and regular aggregate function makes the result different in this case.
select sum(Quantity) over() from orderdetails where Quantity > 2000
select sum(Quantity) from orderdetails where Quantity > 2000
thanks in advance,
Jeopardy
I have report showing sales by Vendor. I need to list all the vendors with Monthly Total>5000 and combine the rest as "OTHER VENDORS"
Vendor is a Group in my report, so I tried to put an expression as a Group on:
=IIF(Sum(Fields!Mth_1_Sales.Value)>5000,Fields!Vendor_No.Value,"OTHER VENDORS")
I've got an error: "aggregate functions cannot be used in group expressions"
How do I get Vendors with Sales < 5000 into "OTHER VENDORS" ?
I am working in a project where the business model is complex enoughthat many common retrieval functions become difficult to develop andmaintain in single query statements or functions.I have found the logic is easier to implement (and later modify whenthe code is no longer freshly remembered), by implementing theprocessing layers in nested views, and functions that callsub-functions (UDFs), not too unlike object based programming ornon-DBMS procedural languages. In some cases, the views and functionsare nested three deep.So far, from a design standpoint, this practice is working very well.Code that would be a recusive mess is reduced to two or three simplercode blocks. With the proper indexing and query structure, performanceseems to be satisfactory (time will tell).In MS SQL Server, is there anything which makes this practice unsound?Examples:CREATE VIEW vw2 AS SELECT * FROM tbl1 WHERE ...CREATE VIEW vw3 AS SELECT * FROM vw2 WHERE ...Application uses: SELECT * FROM vw3-or-CREATE FUNCTION udf2 AS SELECT * FROM tbl1 WHERE ...CREATE FUNCTION udf3 AS SELECT * FROM udf2 WHERE ...Application uses: SELECT udf3(param) AS value
View 5 Replies View RelatedI'm trying to sum a column in a report. in Most columns I can just wrap the row level expression with "Sum()" and it works. However, I have run into a few that give the following error. The Value expression for the text box ‘Textbox241’ specifies a scope that is not valid for a nested aggregate.  The scope must be the same name of the scope specified by the outer aggregate or the name of a group or data region that is contained in the scope specified by the outer aggregate.Here is my row level expression that works.
=Code.Divide(sum(Fields!WeeklyUnits.Value),sum(Fields!EstUnits.Value))
*
(Code.Divide(sum(Fields!EstHours.Value),sum(Fields!EstHours.Value,"Job")))
Here is my attempt to Sum the row level for the footer area.
=Sum(Code.Divide(sum(Fields!WeeklyUnits.Value),sum(Fields!EstUnits.Value))
*
(Code.Divide(sum(Fields!EstHours.Value),sum(Fields!EstHours.Value,"Job"))))
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.
I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named
This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.
The first factor, Â BillableLastYear is a number, aggregated (sum) from child units.
The second factor, Â BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.
How can I calculate This_Year_Billable_Objective_Count for each unit?
\ able 1
SELECT [UnitKey]
   ,[UnitID]
   ,[UnitName]
   ,[Unit2Name]
   ,[Unit3Name]
   ,[Unit4Name]
[Code] .....
I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID
View 9 Replies View RelatedI have a column that has an expression with a runningvalue in it, a "Carrying Cost" for each month. I need to create another column that aggregates the monthly Cost. I can't to do a Runningvalue on the Runingvalue. I can't even do a Sum on the Runningvalue.
View 9 Replies View RelatedI have a table that has 4 colums (id,projectno,date,price)
i want to make a select that returns the sum per project no
i used this query
select projectno,sum(pice) as sum
from supplier
group by projectno
but i want to include additional columns like id and date for the result
but its giving this message:
Column 'supplier.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
is there a better way to do so without joining the main table with the upper select query?
Best Regards
I need to get the sum of a field that already has an aggregate function (MAX) performed on it. I am using the following query
Code Snippet
SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",
MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty",
"tPCT"."BatchKey"
FROM ("vSS" "vSS"
INNER JOIN "tI" "tI"
ON "vSS"."ItemKey"="tI"."ItemKey")
LEFT OUTER JOIN "tPCT" "tPCT"
ON "vSS"."ItemKey"="tPCT"."ItemKey"
WHERE "vSS"."ItemID" = '3002954'
GROUP BY "tI"."ItemID", "tPCT"."BatchKey"
It yields the following results
ItemID
Short Description
Freeze Qty
Current Qty
BatchKey
3002954
SET, WRENCH HEX METRIC
-33
129
42221
3002954
SET, WRENCH HEX METRIC
51
129
42244
3002954
SET, WRENCH HEX METRIC
-31
129
42250
I need to SUM the maximum freeze quantity values per item ID. Therefore for this record, I need the following results:
3002954 SET, WRENCH HEX METRIC -13 129
Can this be done via a subquery? Any assistnance would be greatly appreciated?
Thanks,
DLee
Hi,
I have we have a client who gives their invoices in a flat file format, we import it into a SQL Server table.
Nothing is normalized – everything is repeated in every record. The fields are:
customerNumberInvoice_numberPO_numberQtyDescriptionLine_numberLine_totalFreightTaxInvoice_date
So an if an order has 10 line items, the header information (invoice number, PO number, ivoice date) are repeated on each of the lines
I am writing a query to show the following
Order number, Invoice total, Date
select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice_date from invoices group by invoice_no, Invoice, customerNumber
This works great - for each invoice I get the invoice number, InvoiceTotal, and Date
Then I was asked to add the PO Number – this is where I can’t get it right.
When I added “PO_number� to the query, I got two lines for each invoice
select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice, PO_number from invoices group by invoice_no, Invoice, Sold_To_Cust_No, PO_number
Please help - I need to end up with: invoice_no, invoiceTotal, customerNumber, Invoice_date and PO_number (sequence does not matter)
Thanks