Transact SQL :: Working Of Aggregate In Sub-query Using Exists?
Aug 4, 2015
When i am running below snippet execution plan is showing constant scan instead of referring subquery table.
I want to know how this query working. and why in execution plan there is no scan /seek which will basically indicate that particular table is getting referred.
select count(*) from AÂ where exists (select count(1) from B where A.a=B.a)
execution plan has to show scan or seek for subquery. Surprisingly, output is coming as expected.
I am using SQL Server 2008 - and what I want to do is set my variable @dh. Â If the @startDate and @endDate falls into the criteria for my if exists statement, I want to set @dh equal to datediff(h, logontime, logofftime) BUT if that criteria is not true, I want to set @dh = 24. Â How can I do that?
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1) In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment.  If I do change the query to something below then definately it will break - Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment ( DeptId int identity(1,1) primary key, name varchar(50) ) create table ChildDepartment ( Id int identity(1,1) primary key,
I have a a Group By query which is working fine aggregating records by city. Â Now I have a requirement to focus on one city and then group the other cities to 'Other'. Â Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'Â From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Kansas City 800 Columbia 700 Jefferson City 650 Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'Â From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Other Missouri City 800 Other Missouri City 700 Other Missouri City 650 Other Missouri City 300
(select SUM(sales.Total) from sales where StudentHist.Curdate = max(sales.curdate))  AS 'Balance'Iam trying to write a subquery to  calculate the total amount of sales until the Curdate  in studenthist equals the Curdate in sales table..how to write this query??
I have a table with sample data as shown in the table below. for each month end date, I need to aggregate (sum) all the quantities for each product and customer and include the current month plus the past 12 months (including current month).Â
For example, for customer C123, P123 and for 7/31/2015, the query should add 7/31/2015 quantity and the quantity for 1/31/2015.Â
DECLARE @TEMP TABLE (Customer VARCHAR (10), Product VARCHAR(10), Month_end_date DATE, Quantity INT) INSERT INTO @TEMP VALUES ('C123','P123','1/31/2015',10) INSERT INTO @TEMP VALUES ('C124','P124','2/28/2015',20) INSERT INTO @TEMP VALUES ('C125','P125','3/31/2015',30) INSERT INTO @TEMP VALUES ('C126','P126','4/30/2015',40)
[Code] ....
the output for the above example should be C123Â P123Â 7/31/2015 Â 70+10=80
SSMS does not like mine! Â THis is the error that I receive:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
And this is my syntax:
Select employeeID ,COUNT(case when rehirestatus IN (select rehirestatus from regionalemptable where rtrim(storename) = 'Location1') THEN userID ELSE 0 END) + COUNT(case when rehirestatus IN (select rehirestatus from globalemptable where rtrim(storename) = 'Location1') Then userID ELSE 0 End) FROM production GROUP BY employeeID ORDER BY employeeID
I am using SQL 2005. I have some data from an old application that did not follow the rules for normalization. The table is for Invoices, and the table allows for 13 purchase items per record. So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12. For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc. It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date"). My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.
ACCOU NAME     NAME TODATE                           ID    EDUCAT   EXPIRYDATE 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 2016-06-24 00:00:00.000 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000
Need to get it like
ACCOU NAME     NAME TODATE                           ID    EDUCAT   EXPIRYDATEstring 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000
In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.
Well adding it to a group by or function skews the result set. How to write this query so it displays as I need it to? Â This is what I have thus far, and it works as it should UNTIL I add in the line ofÂ
cast(cte.[C] As float)/cast(sum(cte.[C]) over() as float)*100 As [Rate1],
Presents the error of: Msg 8120, Level 16, State 1, Line 35 Column 'cte.[C]' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
This is my full on query -- with 3 CTE's involved to get me the actual result set I am after. Â
;with cte as ( select [state], case when exists (select 1 from table2 R where R.centername = d.centername) then 1 else 0 end as [L], case when exists (select 1 from table3 C where C.centername = d.centername) then 1 else 0 end as [C] FROM maintable d ),
This is my query, from my nonono table I need to return the most recent date from attempted (column name) Â How could I modify this to do such
Select * FROM [nonono] td WHERE (NOT EXISTS (SELECT [First], [second] FROM [yesyesyes] AS d WHERE ([First] = td.[First]) AND ([second] = td.[second]) AND ([Worksite] = td.[Worksite])))
I am trying to bulk update about 50,000 rows in a SQL table. Â The values that the table MUST contain are:
1-3 days 4-7 days 8-10 days
Some of the rows contain a space between the number and the hyphen like:
1 - 3 days 4 - 7 days 8 - 10 days
What would be my best methodology of removing the space between numerics only? Â I have seen multiple examples of how to remove ALL whitespace, but I only want to remove the space between the numbers and the hyphen *IF* it exists. And the field type is varchar(200)
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
I have a few tables I am trying to join to create a report. Everything was working fine until I tried to add an aggregate Sum function to a column (MaxCap) in table ctfBarn.Â
I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?
Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.
select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >=50 order by ordertotal;
I have a table which has 2 columns and the data is like below
API_Number       Group_Name
1234Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A 3241Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A 1234Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group B 4567Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group C 7896Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group D 3241Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group E
 I wanted to find the API numbers which are repeating in different groups. In the output I want
 API_Number          Group_Name
1234Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A,Group B 3241Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Group A,Group E
What is the best way to drop a temp table if it exists? I am looking something similar to this: if exists (select top 1 * from #TableName) then drop #TableName else end
I have been asked to create PK on many tables using a query on all tables where we do not have clustered indexes. Some of the tables contains PK but non-clustered. If in a table there are no PK, then how to decide on which column PK can be created? can we do it with the query without data loss and without human intervention?
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
Msg 22006, Level 16, State 1, Line 3 xp_subdirs could not access 'ServerBSQL_Backups*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'
How to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store   Type   Sales 11      A     1000 23    A    1980 23    B    50 5     B    560
I want to filter the store in "where clause" Â where
1)- if the store has type A and  B, then assign only A 2)- if the store has  type A associated with it then assign A 3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type from table1 where (TYPE]= Â (case when [TYPE] in ('A','B') then 'A' when [TYPE]='A' then 'A' else 'B'end)) GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
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
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.
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 have two tables ItemHistory and ItemStock. I would like to write a query which checks last years history and let us know if we have enough items in stock this year for a given span date.
First, It should get all items from @ItemHistory where WHERE DateSold >= '1/10/2007' AND DateSold < '1/11/2007' and then checks if corresponding items are found in @ItemStock, and then returns all the ItemID where sum(@ItemStock.Quantity) < sum(@ItemHistory.Quantity)
Thank You.
Here is the DDL and DML
DECLARE @ItemHistory TABLE ( ItemID INT, Quantity INT, DateSold DATETIME ) INSERT INTO @ItemHistory SELECT 12, 18, '2007-01-10' UNION ALL SELECT 12, 18, '2007-01-10' UNION ALL SELECT 26, 12, '2007-01-10' UNION ALL SELECT 28, 06, '2007-01-10' UNION ALL SELECT 29, 06, '2007-01-10' UNION ALL SELECT 30, 06, '2007-01-10' UNION ALL SELECT 31, 06, '2007-01-10' UNION ALL SELECT 31, 06, '2007-01-10' UNION ALL SELECT 32, 12, '2007-01-10' UNION ALL SELECT 33, 01, '2007-01-10' UNION ALL SELECT 33, 06, '2007-01-10' UNION ALL SELECT 36, 01, '2007-01-10' UNION ALL SELECT 52, 12, '2007-01-10' UNION ALL SELECT 83, 01, '2007-01-10' UNION ALL SELECT 36, 12, '2007-01-10' UNION ALL SELECT 37, 01, '2007-01-10' UNION ALL SELECT 38, 12, '2007-01-10' UNION ALL SELECT 17, 01, '2007-01-10' UNION ALL SELECT 17, 08, '2007-01-10' UNION ALL SELECT 12, 20, '2007-02-20' UNION ALL SELECT 26, 10, '2007-02-20' UNION ALL SELECT 30, 08, '2007-02-20' UNION ALL SELECT 31, 12, '2007-02-20'
DECLARE @ItemStock TABLE ( ItemID INT, Quantity INT ) INSERT INTO @ItemStock SELECT 12, 20 UNION ALL SELECT 12, 10 UNION ALL SELECT 14, 48 UNION ALL SELECT 17, 24 UNION ALL SELECT 19, 36 UNION ALL SELECT 19, 72 UNION ALL SELECT 20, 72 UNION ALL SELECT 26, 24 UNION ALL SELECT 28, 12 UNION ALL SELECT 29, 12 UNION ALL SELECT 30, 12 UNION ALL SELECT 31, 18 UNION ALL SELECT 32, 20 UNION ALL SELECT 32, 68 UNION ALL SELECT 33, 10 UNION ALL SELECT 35, 18 UNION ALL SELECT 36, 46 UNION ALL SELECT 36, 40 UNION ALL SELECT 37, 30 UNION ALL SELECT 38, 10 UNION ALL SELECT 38, 33
I have a database table that contains rows containing defect data. The two columns I'm interested in are device and severity. Device is the name of an item that contains the defect, and severity can be one of three values. I'd like to query the table so it shows the device, and the number of instances of each of the three values for that device. For example, the result table should look like this:
I have a query taken from a Crystal Report, and I've been working to modify it for a slightly different purpose. Â The initial report was designed to take an input of an end date, go back to the last day of the previous month, get Actuals (sales totals), then add up all sales, costs, and purchases up to the end date, then display assorted data.
The new query needs to take a begin date and and end date, go back to the last day of the previous month for actuals, add all sales, costs, and purchases to those actuals until it gets to the begin date. Â That is now the new Actual, and we need to sum up all transactions from then to the end date.
My problem lies in the fact that I can't get the query to add up the numbers.
The query is here:
SELECT DailyReport.Store, DailyReport.Report_Date, sum(dailyreport_Detail.sales) as Sales, sum(dailyreport_detail.actual) as Actual, sum(dailyreport_detail.cost) as Cost, CompanyGroups.Category, DailyReport_Detail.Product, CompanyGroups.Retail_Inv FROM (`DailyReport` `DailyReport` INNER JOIN `DailyReport_Detail` `DailyReport_Detail` ON
I have a table similar to the following: ID ¦ Name ID ¦ Period From ¦ Period To ¦ Percentage ¦ --------------------------------------------------------------------------- Important - Each person can have more than one entry. What I am trying to do is get the last percentage that each person obtained. The only way I have been able to do this is by the following: SELECT * FROM myTable LEFT OUTER JOIN ( SELECT NameID, MAX(PeriodTo) as PeriodTo FROM myTable GROUP BY NameID) t1 ON myTable.NameID = t1.NameID WHERE myTable.PeriodTo = t1.PeriodTo
I was wondering if there was another way of doing this, or whether this is an efficient method of doing this kind of query. Jagdip
