A problem about SQL Statement

Hello, I've got a problem about a Select statement with the following criteria:

Data Table

Key Num1 Num2
A 1 2
A 1 2
A 2 3
B 0 0
B 1 1
C 2 1
C 1 2

I would like to select all row of a key if the (sum of num1 - sum of num2) with that key != 0

In the above case, the outcome would be:

A 1 2
A 1 2
A 2 3

Could any body advise what the SQL should be? Many thanks!


1/31/2009 5:23:40 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

8 Replies

Similar Articles

[PageSpeed] 44


Try Like This

I have shown the select query in bold


CReate table Test_Sql

([Key] varchar(20),[Num1] int, [Num2] int)


insert into Test_Sql

Select 'A', 1 , 2

union all

Select 'A', 1, 2

union all

Select 'A', 2, 3

union all

Select 'B', 0, 0

union all

Select 'B', 1, 1

union all

Select 'C', 2, 1

union all

Select 'C', 1, 2




Select t.[Key],t.[Num1],t.[Num2]

from Test_Sql t

inner join (

Select [Key] from


group by [Key]

having (sum([Num1])-Sum([Num2]))<>0

) cte

on t.[Key]=cte.[Key]



Key Num1 Num2

-------------------- ----------- -----------

A 1 2

A 1 2

A 2 3

(3 row(s) affected)


MVP ASP/ASP.Net Read My Blog


"Success doesn't come to you…you go to it."--Marva Collins

"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous

1/31/2009 5:36:53 PM

Try this:


Select YourTable.*
From YourTable
Inner Join (
Select [key]
From YourTable
Group By [key]
Having Sum(Num1) <> Sum(Num2)
) As SumTable
On YourTable.[key] = SumTable.[Key]

 The trick here is.... you want the sum of one column to not equal the sum of another column, so you must put that condition in the HAVING clause.  Then.... I use a derived table to join the key column back to the real table so that we can show all of the data in those rows.

Make sense?

1/31/2009 5:37:41 PM

Something like this?  It should only return all A's

	FROM Data
	HAVING (SUM(Num1) - SUM(Num2)) != 0)
- William

Please mark the most helpful reply/replies as "Answer".

Give some of my PWSK modules a try.
1/31/2009 5:39:20 PM

declare @t table ([key] char(1),Num1 int, Num2 int)  
SELECT 'A',1,2 

--SQL Server 2005/2008

SELECT [key],num1,num2 FROM
(SELECT [key],num1,num2,
SUM(num1) OVER(PARTITION BY [key])-SUM(num2) OVER(PARTITION BY [key]) as sumDiff from @t) t
WHERE sumDiff<>0
;WITH mycte
(SELECT [key],num1,num2,
SUM(num1) OVER(PARTITION BY [key])-SUM(num2) OVER(PARTITION BY [key]) as sumDiff from @t)
SELECT [key],num1,num2 FROM mycte
WHERE sumDiff<>0


1/31/2009 5:39:36 PM


We Put Same Logic At Same Time.....Big Smile


MVP ASP/ASP.Net Read My Blog


"Success doesn't come to you…you go to it."--Marva Collins

"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous

1/31/2009 5:39:36 PM



We Put Same Logic At Same Time.....Big Smile



I know, but you beat me by a couple seconds. :(

I must be getting slow.

1/31/2009 5:41:50 PM

required query is given below

select * from test1
where key1 in
select key1
from test1
group by key1
having sum(num1)-sum(num2)!=0)






Thanks & Regards,
Izhar Ul Islam Khan
Microsoft Certified Technology Specialist
1/31/2009 5:52:50 PM




We Put Same Logic At Same Time.....Big Smile



I know, but you beat me by a couple seconds. :(

I must be getting slow.

You can not win all the times :)

Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
2/1/2009 1:52:22 AM

Similar Artilces:

Problem with analytic sql function (The OVER SQL construct or statement is not supported)
Hi All!  Could You comment the next situation:I'm configuring my TableAdapter just like Scott Mitchell does in his tutorialhttp://www.asp.net/learn/data-access/tutorial-70-vb.aspxThe only principal difference is that I need Insert/update and deletemethods to be generated (His aim is only SELECT).I'm also using analytic function (ROW_NUMBER) and I'm also gettinwarning “The OVER SQL construct or statement is not supported.” Yousay then that it could be ignored. But, in this case statements tomodify data (insert/update and delete) aren't being generated, thoughafter w...

SQL Logging SQL Statement
Hi at All!! I use ASA 7 so I'd like to know if there is a way to logging the sql instruction that database server dbsrv7, receive from clients ? I need this because a client that use odbc connection after performing an insert into a table, the client receive the Message: "SQL truncate". I need to know the field on the table that generate this warning!! Thanks in advance Massimo The tool that does this is called "request-level logging" and in V7 you have to enable it from the command line when you start the server, using the -zr (and optionally -zo) c...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

SQL Server 2005 Installation problems with SQL Server Express & SQL Server 2000
Hi Guys, I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well.  (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!) Everything went fine except that nothing seemed to be installed.  I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing. A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005. So after a number o...

SQL Server Problem.(SQL-28000)
------=_NextPart_000_0033_01C12642.735DD1F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hello, I'm a newbie here and have a question regarding the use of DBI::ODBC with SQL Server. I've checked the mail archives and seen others with the same problem but have yet to come across the solution. Here's what I'm doing: (note: actual user name and password have been changed here). use DBI; my %attr = ( PrintError => 1, RaiseError => 1 ); $dbh = DBI->connect("dbi:ODBC:LocalServer", &...

SQL::Statement problem
I posted a similar message to Jeff directly but I never got a confirmation that he actually received. So let's try here... Firstly, I've found out that RLIKE is broken: sub is_matched { ... if ($op =~ /LIKE|CLIKE/i) { $val2 = quotemeta($val2); $val2 =~ s/\\%/.*/g; $val2 =~ s/_/./g; } If $op i s'RLIKE', and since 'RLIKE' contains the string 'LIKE', the pattern is run through quotemeta(). Also, the SQL operators IS and the LIKE family are case-sensitive, which is contrary to SQL specs if I'm not mista...

SQL-Statement problem
Dear all i am having a problem with SQL-Statement-1.005, whereas SQL-Statement-0.1020 is working fine. SQL ERROR: Mismatched parentheses in WHERE clause! SV = RV(0x810cad0) at 0x829105c REFCNT = 1 FLAGS = (PADBUSY,PADMY,ROK) RV = 0x83844e4 DBD::CSV::st execute failed: dbih_getcom handle 'DBD::CSV::Statement=HASH(0x83844e4)' is not a DBI handle (has no magic) at /usr/local/lib/perl5/site_perl/5.6.0/SQL/Statement.pm line 164. I think i tracked this down to Parser.pm the cleansql routine which skips closing parens, but not the corresponding openings: wa...

Problems with SQL statement
Hello all I have the below SQL statement querying my access database. To me the code looks fine and I can't see what’s wrong with it, but when I execute it I get the syntax error message at the bottom of this post. The code is supposed to take input text from a search field and use it to search selected fields in the database. It then returns these where there are matches and where the quantity is = 1. I'm sure the problem is something really simple but all help would be appreciated. The SQL query: ("Select ProductID, ProductName, " _ & "BriefDescription, ...

Problem with sql statement
Hi all I've got some trouble with a sql statement. What I have got: 4 Tables 1. tFuel------------------------FuelId | FuelDescription ... 1 | Diesel  2 | SomeOtherKindOfFuel 2. tFuelPrice------------------------GasStationId | FuelId | Price ...1 | 1 | 1.451 | 2 | 1.30 3. tGasStation------------------------GasStationId | GasStationGroupId | Name ...1 | 2 | Millers Gas Station |2 | 1 | Chucky's Gas Station | 4. tGasStationGroup------------------------GasStationGroupId | Name ...1 | BP2 | Shell So, what I want to achive is the following I'd like to show the user a list of all...

Problem with IF statement in SQL
 Can someone please tell me what I'm doing wrong here:  CREATE PROCEDURE dbo.AddProductToEnquiry @SessionID int, @ProductID int AS DECLARE @CountProductInEnquiry int SET @CountProductInEnquiry = (SELECT Count(*) FROM Enquiry_Items WHERE SessionID=@SessionID AND ProductID=@ProductID) IF @CountProductInEnquiry = 0 THEN INSERT INTO Enquiry_Items ([SessionID], [ProductID], [Date]) VALUES (@SessionID, @ProductID, GETDATE()) END IF   Cheers,Curt Regards, Curt  Actually I think Ive fixed it. Can someone please conf...

SQL Statement problem
I am trying to pull certain information from a database by using the ISQL window, but Powerbuilder keeps locking up when I type the following code, could someone please show me what I'm doing wrong (assuming that all of the spelling is correct)? Thanks!!! SELECT t0.code, t0.billcode FROM billcode t0 WHERE t0.code in (SELECT distinct t1.code FROM app_charges_old t1 WHERE t1.invoice_number IN (SELECT t2.invoice_number FROM invoice_summary_old t2 WHER...

SQL statement problems
Hey gang, I'm trying to work out why on earth my SQL statement refuses to work. I've tested it on my db within Access, yet working off the website itself, it just refuses to return anything when there should be 7 or so listed. Can anyone see anything wrong with my statement (warning, it's long): strConnection = @"Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + (string) Server.MapPath("GAAP.mdb"); strSqlCommand = "SELECT STUDENT.STUDENT_ID, STUDENT.STUDENT_LAST_NAME, STUDENT.STUDENT_FIRST_NAME, UNIT_STUDENT.STUDENT_EMAIL, UNIT_STUDENT.STUDENT_PASSWO...

SQL statement problems
Having problems with our SELECT statements in both Webforms and client/server application that I wasn't having in the various demo versions. I am using "SNC" (SQL Native Client installed) connection. Connected to DB with transaction object called "SQLCA2". This code for debugging purposes works: select count(*) into :ll from users using SQLCA2; This actual code does NOT work: SELECT count (*) INTO :l_i_count FROM users WHERE user_id = :a_s_user_id using sqlca2; From debugger: sqlcode=0 sqldbcode=0 sqlerrtext="" Any ideas would b...

SQL Statement problem
Can someone please tell me what is wrong with my SQL below..I think I am missing a quote somewhere... "select distinct itype from TR_data_and_history where stationname ='" & StationNameString & " and highkv ='" & VoltageString & "' " StationNameString single quotes are not endedSushila Bowalekar PatelVisual ASP/ASP.NET MVPhttp://weblogs.asp.net/sushilasb Thank you..that was the problem. Final statement = "select distinct itype from TR_data_and_history where stationname ='" & StationNameString & "' and highkv...

Web resources about - A problem about SQL Statement - asp.net.sql-datasource

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from June 2006 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

"Welcome to our home. Please take a moment to review our latest statement regarding so-called micro-aggressions..." ...
"... together with a revised list of 'trigger warning' requests. Kindly commit the following to memory...." Trigger warning for Althouse readers: ...

Liveblogging World War II: Robert H. Jackson: Opening Statement before the International Military Tribunal ...
**Robert H. Jackson**: [Opening Statement before the International Military Tribunal](https://www.roberthjackson.org/speech-and-writing/open ...

Mayoral Candidate Sheila Dixon Releases Statement On Freddie Gray Trial
Former Mayor Sheila Dixon issued a statement on Tuesday regarding the upcoming trial of Baltimore officer William Porter, one of the six officers ...

Protective case turns your sleek iPhone into a loud fashion statement
... iDevice cases are loud and rugged. You might even call them masculine, although I know a few of my female friends would embrace the bold statement ...

Election 2016 News Today Dominated by Media Furor over These Trump Statements
Here is today’s top election 2016 news: The media attacked GOP presidential front-runner Donald Trump this week for saying thousands of New Jersey ...

President Obama delivers a statement on national security
x YouTube Video Wednesday, Nov 25, 2015 · 5:08:06 PM +00:00 · Laura Clawson We’ll have a more in-depth account coming soon, but the summary ...

Why Chancellor George Osborne's Autumn Statement Is Good News For Entrepreneurs
Why the UK Chancellor's statement is mostly good news for Britain's business owners.

New Statement Said to Identify Gunmen in Mali Attack
ABC News New Statement Said to Identify Gunmen in Mali Attack ABC News Fuerzas militares rodean al presidente maliense Ibrahim Boubacar Keita ...

Resources last updated: 11/27/2015 10:02:28 AM