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).
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!
How 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?????
I have several tables that I need to summarize data from two tables based upon a dates passed in and group that data. I have attached my table layout, some sample data, and how I would like the results to look.
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.
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..
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 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.
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.
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;
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
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......
Hi 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!
Do 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
I 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.
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
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
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.
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
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.
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
Hello, i have a table called tblschedule that has field for resourceID, employeeID, and scheduleDate. And also i have another table called tblResource that has resourceID and ResourceName. The third table called tblEmployee has employeeID, employeeFirstName and employeeLastname. I want to get a report for each resourceName (not ResourceID) that per employee schedule (COUNT). I need the report has ResourceName field, employeeName field and count. How can i write a store procedure as it need join three table to get the count. Thank you very much!
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data. When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
I'm having trouble using Aggregate functions with multiple fields. Here is what I'm trying to do:
SELECT REPLACE(Cust_Key, sum(PRODUCT_CHARGE), count(Tracking_Number), Tracking_Number FROM Shipments = '2008-05-05' Group By Cust_Key, Tracking_Number
I can get this to work when I use Group By for a single field like Cust_Key but I don't know how to make it work for multiple fields. What is the best way to write something that contains a few aggregate functions on top of multiple fields?
I am trying to aggrgate Values on three columns Customer , OrderDate and Product thorough SSIS. It gives me following masseges and works very very slow.
Before Aggregation I sort the Data by Customer , OrderDate & Product.
The Aggregate transformation has encountered 4085 key combinations. It has to re-hash data because the number of key combinations is more than expected. The component can be configured to avoid data re-hash by adjusting the Keys, KeyScale, and AutoExtendFactor properties.
The Aggregate transformation has encountered 25797 key combinations. It has to re-hash data because the number of key combinations is more than expected. The component can be configured to avoid data re-hash by adjusting the Keys, KeyScale, and AutoExtendFactor properties.
The Aggregate transformation has encountered 253973key combinations. It has to re-hash data because the number of key combinations is more than expected. The component can be configured to avoid data re-hash by adjusting the Keys, KeyScale, and AutoExtendFactor properties.
The Aggregate transformation has encountered 2000037key combinations. It has to re-hash data because the number of key combinations is more than expected. The component can be configured to avoid data re-hash by adjusting the Keys, KeyScale, and AutoExtendFactor properties.
I have struggled and can't seem to get the SQL correct to get the records I want. I have 2 tables containing information from 2 separate independant sensor arrays which collect data about trains in a rail network. I need to get the most current records about the trains at any given time.
For any given moment in time, I want to query the database and get the last known position of each train and it's status. So I want a result table like this: given time = 10:07:20 TrainId | PosTime | X | Y | StatusTime | SensorId | Delay | OffDuty 109 | 5/10/2007 10:07:15 | 62389 | 25002 | 5/10/2007 10:06:51 | 75 | -5 | N 210 | 5/10/2007 10:07:15 | 65489 | 25432 | 5/10/2007 10:04:54 | 85 | -10 | Y 857 | 5/10/2007 10:07:15 | 62889 | 25983 | 5/10/2007 10:05:21 | 231 | +1 | N
OR given time=10:03:30 1 | 109 | 5/10/2007 10:03:30 | someX | someY | 5/10/2007 10:02:34 | 72 | -3 | N 2 | 210 | 5/10/2007 10:03:30 | someX | someY | null | null | null | null (Since train 210 it hasn't tripped a status sensor yet, we don't know it's status at that time) 3 | 857 | 5/10/2007 10:03:30 | someX | someY | 5/10/2007 10:02:48 | 199 | +2 | N
Any help to achieve these results would be MOST appreciated, Thanks.
I need to calculate MEAN (average), Standard Deviation, Variance, Range, Span & Median for each data column (Cost, Schedule in the test data), where each data column has different selection criteria. I have the calculations working for each column individually (e.g. funcCalcCost, funcCalcSchedule), but I need to return the calculated values as a single data set:
SELECT Dept, Project, AVG(Cost) as Cost_Mean, MAX(Cost) - MIN(Cost) as Cost_Range, .......
WHERE CostFlag = @InputParameter
GROUP BY Dept, Project
The code above works great - but only for a single column. I need to return a dataset like this: Dept Project Cost_Mean Cost_Range D1 D1P1 495 135 D1 D1P2 960 70 D1 D1P3 1375 105
Hi everyone. I am updating a table with aggregate results for multiplecolumns. Below is an example of how I approached this. It works finebut is pretty slow. Anyone have an idea how to increase performance.Thanks for any help.UPDATE #MyTableSET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChangedelse null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),TerminationDate=(select Max(Case When Type = 'N' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),ReHireDate=(select MAX(Case When Code = 'HIRE' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and #MyTable.HRRef=HREH.HRRef )
I'm still fairly new to cubes, so bear with me. I'm trying to figure out if I should include aggregate data (e.g. total employees per facility) in a dimension table or if I should use the finished cube to get the counts (MDX?). Thanks for any help that you can provide.
2) SalesTransactions - multiple rows for each contract - relevant columns: ContractNo, InvoiceDate, Value
3) CostOfSaleTransactions - multiple rows for each contract - relevant columns: ContractNo, TransactionDate, Cost
How do I write a SELECT statement to produce rows containing:
ContractNo ContractName Sum of Value for ContractNo between @FromDate and @ToDate Sum of Cost for ContractNo between @FromDate and @ToDate
Not all Contracts have either Sales or CostOfSales Transactions in the relevant date range and so one or both totals can be zero.
I've written something like:
SELECT CT.ContractNo, CT.ContractName, sum(CT.Value) as TotalValue, Sum(CS.Cost) as TotalCost
FROM Contract CT
INNER JOIN SalesTransactions ST ON CT.ContractNo=ST.ContractNo
INNER JOIN CostOfSaleTransactions CS ON CT.ContractNo=CS.ContractNo
WHERE (ST.InvoiceDate BETWEEN @FromDate AND @ToDate) AND (CS.TransactionDate BETWEEN @FromDate AND @ToDate)
GROUP BY CT.ContractNo, CT.ContractName
The TotalValue and TotalCost figures I get are much higher than expected. I presume this is something to do with the JOINs or WHERE clause. Please can you advise how I get the correct values?
