SQL Question

I have a keyed 'invoice' table, a one-to-many 'invoiceCredit' table 
related to 'invoice' with a foreign key, and another one-to-many 
'invoiceDebit' table also related to 'invoice' with a foreign key:

invoice -< invoiceCredit
invoice -< invoiceDebit

Given these tables, I would like to devise a single SQL Select statement 
to produce a result set where each record of the result set presents:

invoice Id, sum of invoiceCredit amounts, sum of invoiceDebit amounts

for all invoices.

I've done this in steps, but can a single SQL Select statement implement 
this?

Gary
0
Gary
8/18/2008 5:56:44 PM
embarcadero.delphi.database 1294 articles. 0 followers. Follow

2 Replies
600 Views

Similar Articles

[PageSpeed] 9
Get it on Google Play
Get it on Apple App Store

Gary Mrenak wrote:

> I have a keyed 'invoice' table, a one-to-many 'invoiceCredit' table 
> related to 'invoice' with a foreign key, and another one-to-many 
> 'invoiceDebit' table also related to 'invoice' with a foreign key:
> 
> invoice -< invoiceCredit
> invoice -< invoiceDebit
> 
> Given these tables, I would like to devise a single SQL Select
> statement to produce a result set where each record of the result set
> presents:
> 
> invoice Id, sum of invoiceCredit amounts, sum of invoiceDebit amounts
> 
> for all invoices.
> 
> I've done this in steps, but can a single SQL Select statement
> implement this?

  SELECT 
    i.ID,   -- invoice ID column
    (SELECT SUM(ic.amount) FROM invoiceCredit ic WHERE ic.InvoiceID =
i.ID) "SumCredit",
    (SELECT SUM(id.amount) FROM invoiceDebit id WHERE id.InvoiceID =
i.ID) "SumDebit"
  FROM invoice i
  ORDER BY ID

Oracle syntax...


-- 
Peter Below (TeamB)  
Don't be a vampire (http://slash7.com/pages/vampires), 
use the newsgroup archives :
http://www.tamaracka.com/search.htm
http://groups.google.com
0
Peter
8/18/2008 6:36:01 PM
Is it that simple?

I tried it and it works in the 'Firebird' syntax as well! Excellent!

Thanks very much!

Gary

Peter Below wrote:
> Gary Mrenak wrote:
> 
>> I have a keyed 'invoice' table, a one-to-many 'invoiceCredit' table 
>> related to 'invoice' with a foreign key, and another one-to-many 
>> 'invoiceDebit' table also related to 'invoice' with a foreign key:
>>
>> invoice -< invoiceCredit
>> invoice -< invoiceDebit
>>
>> Given these tables, I would like to devise a single SQL Select
>> statement to produce a result set where each record of the result set
>> presents:
>>
>> invoice Id, sum of invoiceCredit amounts, sum of invoiceDebit amounts
>>
>> for all invoices.
>>
>> I've done this in steps, but can a single SQL Select statement
>> implement this?
> 
>   SELECT 
>     i.ID,   -- invoice ID column
>     (SELECT SUM(ic.amount) FROM invoiceCredit ic WHERE ic.InvoiceID =
> i.ID) "SumCredit",
>     (SELECT SUM(id.amount) FROM invoiceDebit id WHERE id.InvoiceID =
> i.ID) "SumDebit"
>   FROM invoice i
>   ORDER BY ID
> 
> Oracle syntax...
> 
>
0
Gary
8/18/2008 6:46:06 PM
Reply:

Similar Artilces:

GENERAL SQL SERVER: Question about the Script that setups the SQL Server Database.
This is the first time I have created a script for Sql Server 2000, and I have run into a few syntax ereas that I am not familar with. Bellow is a section of the script from the Portal server packages. At the end of the sample, is "ON [PRIMARY], Can anyone explain to me what this does and why the primary key isnt setup inside the code itself? I am basically trying to take parts of the script and incorrporate it into my own script and hate added syntax that I dont understand, makes debugging a real pain in the future. CREATE TABLE [dbo].[Portal_Announcements] ( [ItemID] [in...

SQL database question
Hi, Since there is no user interface for creating sql server databases with VWD, can I create databases with Visual studio 1.* and use them with VWD? What about the compatibility and constraints? Otherwise, if I want to create databases programmatically, is the code in VWD different from visual studio 1.*? Is there somewhere where I can find samples? Thanks for any pointer. Char22 You could build a sql script based on modifing the sqlTour or Geekspeak sql scripts and execute a Run command like ex. in the Tour or Geekspeak samples. ...

SQL database question
I have a small question and I`d appreciate an answer. I`m still learning asp.net and I`m having some trouble with my database. I want to access the VideoPath and VideoName from my database, to pass on to my flv player embedded in my page. Can anyone explain exactly how I can do this? Thanks in advance!  Go through very easy and simple to understand ado.net tutorial http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspxKamran ShahidSr. Software Engineer(MCP,MCAD.net,MCSD.net,MCTS,MCPD.net[web])Netprosys Inc.www.netprosys.comRemember to click "Mark as Answer" on the p...

ASP/SQL Database question
Is there anyway of putting a prefix on an primary key field? I'll try explain with an example. tblCodes CodeID [PK] - IntegerCodeNameCodeDescCodeType When a new code is created the ID is simply the next value as you would expect. To help with identifying the codes in my actual application, I would like the ID to be based on the CodeType. For example: There are four types of code (red, green, blue, orange), if when creating a new code the user selects the type red, the CodeID will be "RED\1". If another is made using the type red, it will become "RED\2". The same a...

SQL Database Design Question
Hi, I'm relatively new to SQL, and im just after a few ideas on the best approach to designing databases for a project i am currently working on.  It is a financial application to accumulate debit and credit counts and totals entered into a web form (using DataLists). The problem i am having is how i should setup this database.  I have approx 5 different types of this data to collect, but all collecting the exact same details, so at the moment I have 5 duplicate tables named differently for each of these 5 types to collect the counts and totals.  I'm not sure if this is...

Noob question about SQL database
I have a SQL server on my development box.  I am playing with the TimeTracker starter kit.  In the solutionexplorer, when I right click App_Data / Add new item / SQL Database and click add it errors out with "Connections to SQL Server files (*.MDF) require SQL Server Express to function properly"  I do not have, nor have I ever had SQL Express on this box....is there a way to register my SQL server with Visual Studio?  What am I missing? Thanks, and sorry for the noob question.... The App_Data folder is used by SQL Express and Visual Studio assumes SQL Express is insta...

SQL Database Table Question
  SQL Database Table Question I have a Database Table Filled with 10 columns and about 100 rows that is constantly growing. I’ve noticed that whenever I open up the Table that the Tables sequence integrity is out of order.  Meaning that where (uid  # 15) should be, (uid #24) is in its place. And where (uid # 24) should be (uid # 52) is in its place.  Really strange. Its quite irritating because I have a Datagrid drawing a Select * From this Table. And of course the Datagrid renders out of sequence order as is the Table. Anyway to solve this problem...

SQL and Multiple database question
I have a database, Sybase SQL Server System 11, and I need to update information in a table on it with data from another database (DataFlex) via ODBC. I have been unable to figure out the syntax to make this happen. I'm not even sure if it is possible to use a single SQL statement to do this. Any advice would be greatly appreciated. Thanks, Shell Watt Eagle Direct Denver, CO swatt@eagledirect.com ...

How to create SQL Mobile 2005 database from a MS SQL 2005 database?
Hi guys,I'm trying to find solutions to help me in creating a SQL Mobile 2005 database from a SQL 2005 database, with the same table structure and data.May I know how can I achieve this?Thanks.Regards,Jenson Hi Jenson, Visual Studio 2005 or SQL Server 2005 is able to open SQL Mobile 2005 database. However you need either SQL or .NET code to convert SQL Mobile 2005 database this way. Here is an example from MSDN forums which is to convert to xls instead: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=78640&SiteID=1Zhao Ji MaSincerely,Microsoft Online Community Suppo...

moving an sql server 2005 database to a sql server 2000 database
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?.....   this article explains it. http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html hth,mcm pizzamaker74: I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?..... If both are in the same network or box just register the 2005 with the 2000 cr...

Migrating sql server 2005 express database to sql server 2005 database
Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandyhttp://www.thequinn.infohttp://www.sksdataservices.comhttp://www.infobasket.info Hi Sandy, To move a database from SQL Express to SQL 2005, you can use the following steps. 1. If your database file is attached to the server instance, you will need to detach it first using SQL management studio. If it is under App_Data folde...

Move a Table from one SQL 2005 Database to Another SQL 2005 Database
I created a table in an SQL 2005 database, however, I want to move that table and the data to another SQL 2005 database (within the same server). I am new to SQL 2005 and detailed instructions would be greatly appreciated. Thanks Bill You have two options the first is clean but the second is easier.  Try the links below for details.  Hope this helps. http://msdn2.microsoft.com/en-us/library/ms174335.aspx http://msdn2.microsoft.com/en-us/library/ms188029.aspx  Kind regards,Gift Peddie Thanks, but the documentations is somewhat overwhelming and was hoping of a ba...

passing dynamic SQL questions against a database
Hi Does anyone has a simple example for passing SQL questions from a Text-Box against a SQL database? Thanks in advance//F-J Oops, it was simple, I was just tired, here is how I solved it: -------------------------------------------------------------------- <%@ Page Language="VB" Debug="true" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script runat="server"> Sub Page_Load(Sender As Object, E As EventArgs) End Sub ...

SQL Server Database File vs SQL Server Database -VWD Express
VWD Express 2008, SQL Server Express 2005. I have been using SQL Server Express with ASP and VB for some years - I just create a DB in Management Studio Express, have SQL Server set to 'SQL Server and Windows authentication', and use a connection string with 'sa' and a password (I never have managed to get it working with 'integrated security', but that is probably irrelevant to this post). So I have a list of DB's attached to SQL Server that I can see, attach, detach etc in MSE. I am getting started with .NET. Working through a VWD Express book tutori...

general question for ASP and SQL database
Hi everyone, I've wrote a ASPX page that reads data from my SQL database. The SQL database reside in virtual window server 2003 that has put on the company's network. Everything works fine when the virtual server is on company's network. And now I unjoined the virtual server from the network and ASPX page doesn't work anymore. It seems because it can not get data from SQL server. Is there ways to make ASPX and my SQL server work fine without putting the server on the company's network? Should I setup a physical server and put SQL ser...

Web resources about - SQL Question - embarcadero.delphi.database

West Lothian question - Wikipedia, the free encyclopedia
He illustrated his point by pointing out the absurdity of a Member of Parliament for West Lothian being able to vote on matters affecting the ...

Ohio Removes Question About Driver's Licenses From Job Form
Ohio has removed the question asking job applicants if they have a driver's license from the state's online hiring system.

All-inclusive honeymoon destinations and other travel questions answered
Q: My nephew is getting married in August and has asked for my help planning his honeymoon to the Caribbean. He doesn’t have a lot of money to ...

‘Star Wars: The Force Awakens’ script reveals answers to our burning questions
One of the things many of us did this Christmas was see Star Wars: The Force Awakens in theaters. The first movie in the new Star Wars saga is ...

A big question everyone has about Kylo Ren is clearly answered in Disney's "Star Wars: The Force Awakens" ...
Proceed with caution, you must! There are TFA spoilers ahead. If you've seen "Star Wars: The Force Awakens," you've probably left with a few ...

Question #7 for 2015: What about oil prices in 2016?
Over the weekend, I posted some questions for next year: Ten Economic Questions for 2016 . I'll try to add some thoughts, and maybe some predictions ...

Tom Oates: Quarterback, safety among the biggest questions for Badgers ...
Chippewa Herald Tom Oates: Quarterback, safety among the biggest questions for Badgers ... Chippewa Herald Replacing stellar safeties Michael ...

Seven questions to help you keep your New Year resolutions
We know from bitter experience that New Year's resolutions seem to disappear faster than a cold turkey sandwich. Have you made your New Year's ...

Media Stocks Limp To 2015 Finish Line As Investors Question 2016 Prospects
Media company boards will have to make some difficult choices soon when they decide how much to pay their CEOs for 2015. Will the ones that gave ...

Mark Dantonio Doesn't Have Time For Your Stupid Questions
Michigan State head coach Mark Dantonio wasn’t about to suffer multiple questions from ESPN’s Heather Cox before tonight’s Cotton Bowl playoff ...

Resources last updated: 1/3/2016 2:38:18 PM