.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Sum With SubQuery

Posted By:      Posted Date: August 29, 2010    Points: 0   Category :Sql Server
 
Hi people i am looking for This solution SELECT   funcionarioId, (SELECT SUM(Carga.ValorTotal) FROM CARGA WHERE Carga.ViagemId = Viagem.ViagemId) FROM Viagem i have this Query and it is Working Well and the result is: FuncionarioId | Value 1                   3010.00 2                   6000.00 1                   NULL 2                   50000.00 3                   2556.00 2                   300.00 4                   NULL  What i want is to SUM this SUM(VALUE) for each FuncionarioId, i dont have that column, those values is calculated from (SELECT SUM(Carga.ValorTotal) FROM CARGA WHERE Carga.ViagemId = Viagem.ViagemId) FROM Viagem. Thanks


View Complete Post


More Related Resource Links

Linq Subquery

  

Hi!

I have a query that looks like this in SQL:

SELECT SMP.*
FROM StockMateriaPrima SMP
WHERE SMP.Fecha = (SELECT Max(Fecha)
                    FROM StockMateriaPrima SMP2
                    WHERE SMP2.Id = SMP.Id )


Can anyone tell me how to write this query using LINQ in VB.Net ?

Thanks !


Subquery returned more than 1 value.

  
Hello, I need a gander at this query to figure out what i'm doing wrong. update Performance set pmpg = (select PMPG from Performance_Calculator where EFFDT_Converted = (select CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)) and Performance.DR_CODE = Performance_Calculator.FW_OWN group by Performance_Calculator.FW_OWN, Performance_Calculator.PMPG); basically the performance calculator, in the subquery, does return around 320 rows of data.  I thought the where clause would match up the rows of data and insert the pmpg into the performance table but instead I get subquery returned more than  1 value.  This is true but only if the performance_calculator.fw_own = performance.dr_code wasn't there.  I'm confused on this... any suggestions?

Subquery returned more than 1 value

  
SELECT (SELECT fare_amount FROM fare_tab WHERE (class_code = 'ECO')) AS Economy, (SELECT fare_amount FROM fare_tab AS f2 WHERE (class_code = 'TOU')) AS Tourist FROM fare_tab AS f1 I always received an error of: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. How can I solve this?

How to write query with subquery more efficient

  
Hello, I have this query which generates sick days. I have a sick start date and end date, and by joining it with a date table I get all sick days for each employee. But as you can see i select a number of fields, but in the subquery i select a number of fields which are the same. Is it possible to write it more efficient? Thans in advance! SELECT z .ziekmeldingID, z .hrmID, z .ZiekStartDatum, z .ZiekEindDatum, z .ZiekStartTijd, z .ZiekEindTijd, z .ZiekOpen, z .ZiekTypeID, z .ZiekRedenID, z .ZiekStart_KEY, z .ZiekEind_KEY, z .Verzuimduur, D .Datum AS ZiekDag,   CAST(CONVERT(char(8), D.Datum, 112) AS int) AS ZiekDag_KEY, z .hr301_date, z .PercentageZiek, z .PercentageHersteld FROM     (   SELECT dbo .hr300.hr300_obj AS ziekmeldingID, dbo .hr300.hr300_date_start AS ZiekStartDatum,   CASE   WHEN dbo.hr300.hr300_date_end IS NOT NULL   THEN dbo.hr300.hr300_date_end   ELSE getdate()   END AS ZiekEindDatum, dbo .hr300.hr300_time_start AS ZiekStartTijd, dbo .hr300.hr300_time_end AS ZiekEindTijd, dbo .hr300.hr300_open AS ZiekOpen, dbo .hr300.hr104_obj AS ZiekTypeID, dbo .hr300.hr038_obj AS ZiekRedenID, dbo .hr300.hr200_obj AS hrmID,   CAST(CONVERT(char(8), dbo.hr300.hr300_date_start, 112) AS int) AS ZiekStart_KEY,   CAST(CONVERT(char(8), dbo.hr300.hr300_da

Error: 'SubQuery Returned More than 1 Value'

  
I have some code that calls a stored procedure on SQL Server 2005 using the Microsoft JDBC driver 1.1.  The code normally works however, every once in a while an exception is thrown: Code Snippet com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value.  This is not permitted when the subquery follows =, !, <,<=, >, >=, or when the subquery is used as an expression.Generally, this has been resolved by restarting SQL Server 2005, but why is it showing up to being with?Stored Procedure: Code Snippet ALTER Procedure [dbo].[addRecord]@userID int,@itemID int,@info varchar(50),@comment varchar(50),@output int outputASDeclare @dateSubmitted datetimeset @dateSubmitted = getDate();--Insert the new record. THIS TABLE has an ID identity Primary Key --column that auto-increments.insert into RecordTable(UserPerson, Information, DateSubmitted)values (@userID, @info, @dateSubmitted);---Get the ID Assigned in the record table.  The Item table ---has a Foreign key on this column.Declare @assignedID  intset @assignedID = (select ID from RecordTable where DateSubmitted = @dateSubmitted)/**Set the output parameter.*/set @output = @assignedID;--Now update the Item Table.insert into Item (ID, RecordID, Comment)values (@item,@assignedID, @comment);

insert query with subquery

  
Hello, I have 2 tables, one fact table (FeitDienstDagen) which contains employments, and a reference/dimension table, vDimMedewerker,  which contains a history of employees and their departments. IN the fact table I want to insert the correct WerkID from vDimMedewerker. So the employeeID (ms120_obj) must be same,  and the DienstDag_KEY (int date Key) must be greater then or equal to  the first werk_start_KEY (int date key) for that employee,  Ordered descendant. How to do that? Her my query, but it doesn't do the job :-) USE TEAMSOFT_TBSDB GO DELETE FROM dbo.FeitDienstdagen GO INSERT INTO [TEAMSOFT_TBSDB].[dbo].[FeitDienstdagen]            ([DienstverbandID]            ,[Deeltijdfactor]            ,[DienstDag_KEY]            ,[MutatieID]            ,[DienstverbandStart]            ,[DienstverbandEind]            ,[UrenWeek]            ,[Contracturen]            ,[UrenDag]     

problem with subquery.

  
I am having trouble writing this query using row_number. please help SELECT cust.IPDCCode, cust.remaindervalue, planvalue, cust.taccode FROM personprofile cust WHERE cust.profileid = 312456 AND cust.profileurn = ( SELECT MAX(profileurn) FROM personprofile WHERE profileid= 312456 AND reviseddate= '2009-02-22 00:00:00.000' )

Stored Procedure Subquery Issue

  
Hi, In the below stored procedure, I often get this error message... "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." Why is this? I check for multiple entries and delete any that exist. Yet still gives that error on occassion.  Thanks. ALTER PROCEDURE [dbo].[MainTbl] -- Add the parameters for the stored procedure here @Name nvarchar(5), AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Begin Transaction DECLARE @DetailsColour nvarchar(6); -- Insert statements for procedure here Begin -- Details DECLARE @CountDetails int; DECLARE @FirstEntryDetails int; SET @CountDetails = (SELECT COUNT (Name) FROM [DetailsTbl] WHERE Name = @Name ) if (@CountDetails > 1) Begin SET @FirstEntryDetails = (SELECT TOP(1) NameID FROM [DetailsTbl] WHERE Name = @Name ORDER BY NameID ASC) DELETE FROM [DetailsTbl] WHERE Name = @Name AND (NameID != @FirstEntryDetails) End Set @DetailsColour = (SELECT DetailsColour FROM [DetailsTbl] WHERE Name = @Name) if @DetailsColour is null begin set @DetailsColour = 'RED' end DECLARE @FoundData int; SET @Found

Subquery returning more than 1 value

  
Hey Forummers,I have a problem regarding this code:select NotificationID, (select u.UserName from aspnet_Users u, notifications n where u.UserId = n.FromID) as 'UserName', [Message], DateCreated, TypeID from Notifications n, aspnet_Users u where ToID = '92FBC025-5E08-4533-AF36-BF208391E4D6' and Accepted = 0SELECT NotificationID, (select u.UserName from aspnet_Users u, notifications n where u.UserId = n.FromID) AS 'UserName', [Message], DateCreated, TypeIDFROM Notifications n, aspnet_Users uWHERE ToID = '92FBC025-5E08-4533-AF36-BF208391E4D6' AND Accepted = 0The problem lies in the code in bold. and the error message is as follows:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.I have a table called Notifications which has ToID which is a uniqueidentifier of the receiver and FromID which is a uniqueidentifier of the sender.What I want to achieve is to return a set of notifications to the client side according to the ToID of the table, but I do not want to display the FromId GUID in the client where the ToID is linked to. Instead I want to display a username in place of the FromID GUID which field is located in the aspnet_Users table.Any help would be appreciated and thanks in advance... =)CheersZeph

How to use SQL Hints within CTE/subquery

  
Hi, I am trying to use the SQL Server Hints in CTE or in subquery but it is giving me the syntax error. Below is the sample code.   WITH Page AS ( SELECT * FROM Table_1 INNER JOIN Table_2 ON Table_1.c1=Table_2.c1 WHERE Table_1.c2='sample' OPTION (FORCE ORDER ); )   Regards, Jigs

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

  

How do i get around this???

select

 

sum(PODet.UnitCost*ReceiverDet.Qty2Receive) as TotalCost

 <

record count for subquery which is used for INSERT

  
My current code is like this

Insert into ReportDetail( Partcipantid, Reportid)

      select distinct ParticipantID , 3 from abc   where

        closed = 0 and ( isnull ( primaryphone, '' )

Inserting foreign key value using subquery

  

Hi,

 

I'm playing around with a training-wheel database.  I have a table that stores magazine subscription info.  I have a lookup table that stores the delivery period, ie. 'weekly', 'monthly', etc.  I have a primary key ID field in tblPeriods and a foreign key field in the main table, tblMags.  I want to insert data into tblMags without having to memorize the correlation between the period key and the period itself.  Here's what I tried first:

 

INSERT INTO tblMags
 ( vcName,
  fkPeriodID )
 VALUES
 ( 'The New Yorker',
  (SELECT  tblPeriods_ID FROM tblPeriods
   WHERE vcPeriod = 'Weekly'));

 

This gives the following message:

 

Server: Msg 1046, Level 15, State 1, Line 6
Subqueries are not allowed in this context. Only scalar expressions are allowed.

 

I'm thinking there must be a way to insert data like this and take advantage of a foreign key relationship without looking up a value each time.  Any ideas?

 

Mike

 

 

 


Subquery filter (how?)

  


SELECT     fec_doc, num_doc, fec_ven, monto, (SELECT sum(monto) as aas FROM cxc_op_m) r
FROM         cxc_op_m t
WHERE     (fec_doc <= ?) AND (cod_cli_f = ?) AND r.fec_doc <= t.fec_doc
GROUP BY num_doc, fec_doc, fec_ven,Helle monto

Hello!

Can't handle a running sum.

This query works, but without filters.

SELECT     t.fec_doc, t.num_doc, t.fec_ven, t.monto, (SELECT sum(monto) as runningsum FROM cxc_op_m) 
FROM         cxc_op_m t
GROUP BY num_doc, fec_doc, fec_ven, monto


when i try this above do not work.

selecting a value of one column based on the max value of another in a subquery

  

I would like to select the value of column c  that is based on the max of column b

select q.a,q.c

(select a,max(b) as mx,c

from  a table group by a,c)q

when I try to select the row I get multiple rows

I need to return one row that corresponds to the max in col b


SQL PROCEDURE cannot perform aggregate function on expression containing aggregate or subquery

  

Hi I want do SUM(TABLE1.PARAM1)*(SELECT TABLE2.PARAM FROM TABLE 2 WHERE TABLE2.PARAM3=TABLE.PARAM3

I DID BELLOW CODE BUT GETTING ERROR AS cannot perform aggregate function on expression containing aggregate or subquery

YOUR HELP REALLY APRECIATED

 

ALTER PROCEDURE ShowcClientvsTimevsCost(@Country nvarchar(15) )

AS
select  tTIMESHEET.ACTIVITY , sum(REV_BY_CLIENT.Jan+REV_BY_CLIENT.Feb+REV_BY_CLIENT.Mar+REV_BY_CLIENT.Apr+REV_BY_CLIENT.May+REV_BY_CLIENT.Jun+REV_BY_CLIENT.Jul+REV_BY_CLIENT.Aug+REV_BY_CLIENT.Sep+REV_BY_CLIENT.Oct+REV_BY_CLIENT.Nov+REV_BY_CLIENT.Dec) as SUMREVENUE ,
sum(tTIMESHEET.SUN+tTIMESHEET.MON+tTIMESHEET.THU+tTIMESHEET.WED+tTIMESHEET.THU+tTIMESHEET.FRI+tTIMESHEET.SAT)as SUMTIME ,
sum(((tTIMESHEET.SUN)+tTIMESHEET.MON+tTIMESHEET.THU+tTIMESHEET.WED+tTIMESHEET.THU+tTIMESHEET.FRI+tTIMESHEET.SAT)*
(SELECT HR_CHARGE  FROM tEMPLOYEE t  WHERE  t.EMP_ID =tTIMESHEET.EMP_ID ) )as SUMTIMECOST
from tTIMESHEET INNER JOIN REV_BY_CLIENT on
tTIMESHEET.ACTIVITY = REV_BY_CLIENT.Client and REV_BY_CLIENT.Country = @Country
group by ACTIVITY
RETURN 


How to validate invalid column in subquery in script

  
How to validate invalid column in subquery in script.
What i have a full script for all of my stored procedures and function.
In some stored procedures there are some inner/sub queries which have invalid column name but there is no error in the script when i run it or Parse it.IS tehre anyway i can find the error in my script without going in each of Stored procedures/Functions

Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend