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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
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



I have a query that looks like this in SQL:

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???



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




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:


 ( vcName,
  fkPeriodID )
 ( '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?






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


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



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



ALTER PROCEDURE ShowcClientvsTimevsCost(@Country nvarchar(15) )

group by ACTIVITY

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])
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