Is it possible to have a where clause (or other method) where you only select the max value from this: SUM(ORDER_ITEM.ItemQuantity) and only output that 1 row... or even perhaps a range of rows... in others words... find the ItemID with the greatest combined Quantity
Heres the query so far:
SELECT ORDER_ITEM.ItemID, SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
Hi, I need to pass a lsit of items to a query which will be used using IN clauslike campus_code IN (@CampusCodes) . IF the @CampusCodes is "SD34, FHT34, JK678", the SQL server is not returning any rows back. If the @CampusCodes is only "SD34", the SQL server is returning rows back as it suould be. How can fix it so that i can pass more than one value in CampusCodes variable? Thanks
I have a rather long and ugly query that joins seven or eight tables, does some aggreagaions and a union.
It takes about a minute to return 300,000 rows. When I add a where condition which uses a non-key integer field it pares the processing down to 20 seconds to get 2,000 rows. However if I add a second non-key integer field to my where claus it takes just under 30 MINUTES to produce a similar result set.
This is so far out of whak that i don't even know where to start to look.
Here is the query (and I just got this this morning so I haven't worked on it to tune it - I already see a few areas I want to change):
SELECT AMTRANHDR.SMBNKNumber, AMTRANHDR.AMACTNumber, AMTRANHDR.AMALTNumber, POORDERHDR.POORHNumber, AMTRANHDR.AMTRHNumber, AMTRANHDR.AMTRHType, AMTRANHDR.AMTRHSubType, AMTRANHDR.AMTRHCode, AMTRANHDR.AMTRHDesc, AMTRANHDR.AMTRHDate, AMTRANHDR.AMTRHAmt, AMTRANHDR.AMTRHDueDate, AMTRANHDR.AMTRHDiscAllowed, AMTRANHDR.AMTRHDiscDate, AMTRANHDR.SMBCHNumber, AMTRANHDR.AMTRHMasterCode, isnull(TotalBalance.BalDue,0) BalDue, isnull(TotalAmtNoDiscAdj.PaidAmt,0) as PaidAmt, isnull(TotalDisc.DiscTaken,0) as DiscTaken, isnull(TotalAdj.AdjTaken,0) as AdjTaken, AMTRANHDR.AMTRHRecvShip, CASE WHEN AMTRANHDR.AMTRHStatus = 'P' THEN 'Paid' WHEN AMTRANHDR.AMTRHStatus = ' ' THEN 'Open' WHEN AMTRANHDR.AMTRHStatus = 'S' THEN 'Select' WHEN AMTRANHDR.AMTRHStatus = 'H' THEN 'Hold' WHEN AMTRANHDR.AMTRHStatus = 'C' THEN 'Conversion' end as AMTRHStatus, AMTRANHDR.POORRNumber, POORDERREL.POORDNumber, POORDERDTL.POORHNumber, POORDERHDR.POORHCode, POORDERHDR.POORHCode + '-' + POORDERDTL.POORDSeq + '-' + POORDERREL.POORRSeq AS ReleaseNumber, Payments.LastPaymentDate, Payments.PaymentCount, SMCODEBCH.SMBCHStatus, AMALTERNATE.AMALTCode, AMALTERNATE.AMALTName FROM [dbo].AMTRANHDR LEFT OUTER JOIN [dbo].AMALTERNATE ON AMTRANHDR.AMALTNumber = AMALTERNATE.AMALTNumber LEFT OUTER JOIN [dbo].SMCODEBCH ON AMTRANHDR.SMBCHNumber = SMCODEBCH.SMBCHNumber LEFT OUTER JOIN [dbo].POORDERREL ON AMTRANHDR.POORRNumber = POORDERREL.POORRNumber LEFT OUTER JOIN [dbo].POORDERDTL ON POORDERREL.POORDNumber = POORDERDTL.POORDNumber LEFT OUTER JOIN [dbo].POORDERHDR ON POORDERDTL.POORHNumber = POORDERHDR.POORHNumber LEFT OUTER JOIN [dbo].ICINVENTORY ON AMTRANHDR.AMTRHNumber = ICINVENTORY.AMTRHNumber LEFT OUTER JOIN (select amtrhnumber, 0 as BalDue from AMTRANHDR where AMTRHType = 'P' UNION select AMTRANHDR.AMTRHNumber, AMTRHAmt - ISNULL(PaidTran.BalDue,0) as BalDue FROM AMTRANHDR LEFT OUTER JOIN (SELECT AMPMTCROSS.AMPMCItem, sum(isnull(AMPMTCROSS.AMPMCAmount,0) + isnull(AMPMTCROSS.AMPMCDiscount,0) + isnull(AMPMTCROSS.AMPMCAdjust,0)) as BalDue FROM [dbo].AMPMTCROSS GROUP BY AMPMTCROSS.AMPMCItem) as PaidTran ON PaidTran.AMPMCItem = AMTRANHDR.AMTRHNumber where AMTRHType = 'T') AS TotalBalance ON AMTRANHDR.AMTRHNumber = TotalBalance.Amtrhnumber LEFT OUTER JOIN (SELECT AMPMTCROSS.AMPMCItem, sum(isnull(AMPMTCROSS.AMPMCAmount,0)) as PaidAmt FROM [dbo].AMPMTCROSS INNER JOIN [dbo].AMTRANHDR ON AMPMTCROSS.AMPMCCheck = AMTRANHDR.AMTRHNumber GROUP BY AMPMTCROSS.AMPMCItem) AS TotalAmtNoDiscAdj ON TotalAmtNoDiscAdj.AMPMCItem = AMTRANHDR.AMTRHNumber LEFT OUTER JOIN (SELECT Count(0) AS PaymentCount, AMPMCItem, MAX(AMTRHDate) AS LastPaymentDate FROM [dbo].AMPMTCROSS INNER JOIN [dbo].AMTRANHDR ON AMPMTCROSS.AMPMCCheck = AMTRANHDR.AMTRHNumber GROUP BY AMPMTCROSS.AMPMCItem) AS Payments ON AMTRANHDR.AMTRHNumber = Payments.AMPMCItem LEFT OUTER JOIN (SELECT AMPMTCROSS.AMPMCItem, sum(isnull(AMPMTCROSS.AMPMCDiscount,0)) as DiscTaken FROM [dbo].AMPMTCROSS INNER JOIN [dbo].AMTRANHDR ON AMPMTCROSS.AMPMCCheck = AMTRANHDR.AMTRHNumber GROUP BY AMPMTCROSS.AMPMCItem) AS TotalDisc ON AMTRANHDR.AMTRHNumber = TotalDisc.AMPMCItem LEFT OUTER JOIN (SELECT AMPMTCROSS.AMPMCItem, sum(isnull(AMPMTCROSS.AMPMCAdjust,0)) as AdjTaken FROM [dbo].AMPMTCROSS INNER JOIN [dbo].AMTRANHDR ON AMPMTCROSS.AMPMCCheck = AMTRANHDR.AMTRHNumber GROUP BY AMPMTCROSS.AMPMCItem) AS TotalAdj ON AMTRANHDR.AMTRHNumber = TotalAdj.AMPMCItem where AMTRANHDR.AMTRHDeletedOn is null and AMTRANHDR.ADDIVNumber = 8 and AMTRANHDR.AMACTNumber = 21179
and AMTRANHDR.AMALTNumber = 23195 --THIS IS WHERE I RUN INTO TROUBLE (either alone take 20 seconds, together 30 minutes)
I there a way to differ the filtering field through a Case expression in the Where claus? ex: Where Case When @StuNum = '' Then S.SSN = @SSN Else S.StuNum = @StuNum End And ... The actual field to filter by differs, but I can't seem to find the right syntax to do this. Any help is appreciated : )
I 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.
I 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
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
I am attempting to wrie a query that will return aggregate totals from two different tables. The problem is that the TotalForecast totals are way to high. How do I write a query to obtain the correct totals?Table 1 - dbo.QM_ResultsColumns - dbo.QM_Results.Special8, dbo.QM_Results.SessionName, dbo.QM_Results.PersonNumberTable 2 - dbo.PM_ForecastViewColumns - dbo.PM_ForecastView.Hierarchy, dbo.PM_ForecastView.ForecastSelect substring(dbo.QM_Results.Special8,0,6) AS Hierarchy, substring(dbo.QM_Results.SessionName,0,11) As CourseCode,count(dbo.QM_Results.PersonNumber) TotalAssociates,sum(dbo.PM_ForecastView.Forecast) TotalForecastFrom dbo.QM_Results INNER JOIN dbo.PM_ForecastView ON dbo.PM_ForecastView.Hierarchy = substring(dbo.QM_Results.Special8,0,6)where SessionMid in ('96882139', '23620891', '45077427', '29721437')AND substring(dbo.QM_Results.Special8,0,6) in ('EZHBA')Group By substring(dbo.QM_Results.Special8,0,6),substring(dbo.QM_Results.SessionName,0,11)Sample of data returned with my current query.Hierarchy CourseCode TotalAssociates TotalForecastEZHBA CARD167200 1179 141480EZHBA CARD167201 1416 169920EZHBA CARD167202 1119 134280EZHBA CARD167204 99 11880Results when I run aggregate query separatelyActual Total takenHierarchy CourseCode TotalTakenEZHBA CARD167200 393EZHBA CARD167201 472EZHBA CARD167202 373EZHBA CARD167204 33Forecasted Total takenHierarchy CourseCode ForecastEZHBA CARD167200 999EZHBA CARD167201 900EZHBA CARD167202 800EZHBA CARD167204 800
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.
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 was doing a SUM on my returned rows and i found that what i really want is an aggregate bitwise OR on all the returned rows. Do you know what's the function for that?
I have two tables tb1 with item and qtyOnHand and a second table tb2 with item and qtyOrdered I am trying without success to make this happen;select sum (onHand-Ordered) from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as OrderedI kind of gathered it would work based on this http://weblogs.asp.net/jgalloway/archive/2004/05/19/135358.aspxI have also tried this;select tb1.item from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as Ordered, sum (onHand-Ordered) as available from tb1 where tb1.item = RD35Any ides, there are multiple rows of each item in each table tb1 is inventory with several different locations and tb2 is an orders table.
What I'm trying to solve: I have an application that generates SQL queries, and sometimes uses DISTINCT where the result set has no dupe rows. In terms of database resources, I'm trying to figure out if it's worth it to change to app to be smart enough to not use DISTINCT where it won't serve any purpose, or whether to let it do the DISTINCT and save added complexity to the query building application. I.e. what is the cost of DISTINCT where there are no dupe rows?
What I want to know: Can someone explain how the stream aggregate operator actually goes about doing its work?
Does this always create a temp table for sorting and discarding duplicates (for DISTICNT)? If the answer is "no or sometimes", how does it do so in the case where a temp table is not involved? I noticed the the estimated I/O for this operator was zero for some queries I wrote agains pubs. Does this mean that the optimizer believes the temp table needed will fit in-memory and creates it in-memory? Or does the estimated I/O figure not included disk writes for work tables?
I was told that on Oracle there's something called an Aggregate Navigator which should be capable of changing the table you're addressing in a query to another table (with aggregate data) and in this way optimize performance in a data warehousing environment.
I need to run a query to get the following result(by carrier and for each calc_date, calculate the percentage of all individuals who have rcf greater than 0.73):
carrier,calc_date,count of ind with rcf > 0.73, count of all individual, percentage of individuals with rcf's greater than 0.73.
does anyone have an idea of how to achieve that result?
How can I aggregate a top 5 count across two satellite tables?
e.g. Orders and downloads table each have multiple entries for the same customer ID I would like to count the orders and add them to the downloads count too e.g. 5 orders added to 10 downloads giving 15 as the total for this customer and get a total 'site activity' result which I would like to select the top 5 for.
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.
I need to find an aggregate for several fields in a row e.g. Max(date1, date2, ..., dateN)
I can pass this to a delimited string, pass the string to an UDF that returns a table and run Max(tablefield) on that UDF
Unfortunately I can only get this working for 1 delimited string at a time
Ideally I would want to include the function in a SELECT statement, e.g. something like
SELECT t1.a, dbo.MaxOfFieldValues(t1.d1+','+t1.d2+...+','+t2.dN ) FROM t1
I got it working with the following two udfs, but I am sure visitors here have solved this a bit smarter:
ALTER Function [dbo].[MaxOfFieldValues] ( @ListOfValues varchar(8000) , @delimiter varchar(10) = ',' ) RETURNS VARCHAR(8000) AS BEGIN --Need to get the maximum changedate first --pass the fields as one value (a delimited string) --and calc the max declare @result varchar(8000) declare @remainder varchar(8000) set @remainder = @ListOfValues declare @NoOfItems int --items = delimiters +1 SET @NoOfItems = (len(@ListOfValues) - Len(Replace(@ListOfValues,@delimiter,''))/Len(@ListOfValues))+1 declare @counter int set @counter =1 set @result = dbo.TakePart(@remainder,@delimiter,@counter) WHILE @counter <= @NoOfItems BEGIN set @counter = @counter + 1 IF @result < dbo.TakePart(@remainder,@delimiter,@counter) BEGIN SET @result = dbo.TakePart(@remainder,@delimiter,@counter) END END RETURN (@result) END
ALTER FUNCTION [dbo].[TakePart] ( @param varchar(8000) , @delimiter varchar(10) , @NumPart int ) RETURNS varchar(8000) AS BEGIN --Note: maybe smarter to whack the delimiter to the end of the string to avoid the IF statement declare @result varchar(8000) declare @remainder varchar(8000) declare @counter int set @result = '' set @remainder = @param set @counter = 1 WHILE @counter < @Numpart BEGIN SET @remainder = SUBSTRING(@remainder,CHARINDEX(@delimiter,@remaind er,1)+Len(@delimiter),8000) SET @counter = @counter +1 END
IF @counter > (len(@param) - Len(Replace(@param,@delimiter,''))/Len(@delimiter)) BEGIN SET @result = @remainder END ELSE BEGIN SET @result = LEFT(@remainder,CHARINDEX(@delimiter,@remainder,1) -1) END
I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.
One special condition is as follows:
For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored. If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used. Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.
I have implemented a login audit on a particular system which catches the users login details, including their application logon name and NT username.
What I want to do is report on users who have logged on to the software using someone else's workstation (i.e. logged on to more than one workstation).
--Insert test data. Please note that loginName and ntUsername are rarely the same INSERT INTO @logins (loginName, ntUsername, loginDate) SELECT 'Amy', 'Amy', '20070101' UNION SELECT 'Amy', 'Amy', '20070102' UNION SELECT 'Amy', 'Amy', '20070103' UNION SELECT 'Bob', 'Bob', '20070101' UNION SELECT 'Bob', 'Bob', '20070102' UNION SELECT 'Bob', 'Amy', '20070103' UNION --Bob has logged on using 2 different NT accounts SELECT 'Cal', 'Cal', '20070102' UNION SELECT 'Cal', 'Amy', '20070102' UNION --So has cal SELECT 'Dom', 'Dom', '20070102' UNION SELECT 'Dom', 'Dom', '20070102'
Any ideas? I just can't think of the logic needed to get what I want.
Code: [SQL] SELECT DTZZ, [NUM], [ENER], MAX ([MYDATE]) AS max_date
[Code] .....
[Err] 42000 - [SQL Server]An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
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 want to return only the sum total of each of the following two columns generated by this query, but when I wrap them in SUM() I get an error stating that I can't use an aggregate function on an aggregate or subquery.
Is there another approach that I might take to sum these?
SELECT CASE soitem.fmultiple WHEN 1 then (SELECT funetprice FROM sorels WHERE (sorels.fsono = shmast.fcsono) AND sorels.frelease = SUBSTRING(shitem.fsokey,10,3) AND sorels.fenumber = shitem.fenumber) * shitem.fshipqty ELSE (SELECT top 1 funetprice FROM sorels WHERE (sorels.fsono = shmast.fcsono) AND sorels.finumber = soitem.finumber) * shitem.fshipqty END as ExtPrice,
CASE CAST((shitem.fshipqty) as int) % nullif(CAST(inmast.fnusrqty1 as int),0) WHEN 0 then (CAST((shitem.fshipqty) as int) / nullif(CAST(inmast.fnusrqty1 as int),0)) ELSE (CAST((shitem.fshipqty) as int) / nullif(CAST(inmast.fnusrqty1 as int),0)) + 1 END as BoxCount
FROM shmast INNER JOIN shitem ON shmast.fshipno = shitem.fshipno INNER JOIN soitem ON (soitem.fsono = shmast.fcsono) AND (Convert(Int,soitem.finumber) = Convert(Int,SUBSTRING(shitem.fsokey,8,10)) / 1000) LEFT JOIN somast ON (shmast.fcsono = somast.fsono) LEFT JOIN inmast ON (soitem.fpartno = inmast.fpartno) WHERE (shmast.fbl_lading='00000000000000003784') AND (shitem.fshipqty > 0)
I would like to find the first transaction_date with the criteria below and return all transactions after. i would need to use the seqn number since many of the transactions could occur on the same day.
SELECT c.MEMBER_TYPE , c.DATE_ADDED , h.ID , h.ACTIVITY_TYPE , h.TRANSACTION_DATE , h.UF_1 , min (h.seqn) FROM
I've written a query which was working fine prior to me trying to add a sub-query. I'm trying to make sure I am getting the max disconnect date for each account that has been issued a refund. When I attempt to run the modified query, I receive the following error:
Msg 147, Level 15, State 1, Line 43 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Here's the whole query:
IF OBJECT_ID('TEMPDB..#TMP1A') IS NOT NULL DROP TABLE #TMP1A SELECT BC.ACCTCORP, BD.HOUSE, BD.CUST, BC.BATCH,
from Orders join Customers on Orders.CustID = Customers.CustID where OrderDateTime >= '6/1/2006' and OrderDateTime <= '6/30/2006' order by SalesID, OrderDateTime
