Using a Join with Grouped Data Rows

I have two tables, invoices and deposits.

They look a bit like this:

INVOICES

id    |    paymentType     |     grossTotal   |    dateTime
1     |         Cash       |        1000      |   UNIX TIME    
2     |         Card       |        1350      |   UNIX TIME   
3     |         Card       |        1250      |   UNIX TIME   
4     |         Card       |        750       |   UNIX TIME 

DEPOSITS

id    |    paymentType     |       invNo      |    dateTime    |     amount
1     |         Cash       |         1        |    UNIX TIME   |       150
2     |         Card       |         2        |    UNIX TIME   |       350

The deposits are always past dates, and the invoice dates will be, for example today, so I want to determine the balance paid today on an invoice, ie, invoices.grossTotal - deposits.amount, and list by payment Type.

So, in the table example above, there is £850 would have been paid on Invoice 1 and £1000 on invoice 2, this is simple to achieve with one or two rows of each, but when grouping payment types and deposit invoices I am stuck...

SELECT 
   invoices.id, 
   sum(grossTotal)-IFNULL(depositsCheck.previouslyPaid,0) as todayTotal, 
   depositsCheck.previouslyPaid, sum(grossTotal) as grossTotal  
FROM `invoices`    
    LEFT JOIN (SELECT SUM(amount) as previouslyPaid, invNo 
    FROM deposits 
    GROUP BY invNo) depositsCheck ON depositsCheck.invNo=invoices.id 
GROUP BY  invoices.paymentType ORDER BY id DESC

The SQL Query above, will work for the item paid for with CASH, but not for the Card payments, because, grouping invoices.paymentType means that the id column from the invoices table is no longer correct, so the JOIN fails if this row has an id to which no deposit relates.

How can I run a query as above, but ensuring that I can join the deposits table on any instance of an invoice, grouped by payment type that matches the grouped column id records?

I am using mySql, so please post joins that MySql can do! :D

Answers


The problem is that when you use GROUP BY, you can only SELECT aggregates and the columns you have grouped on.

invoices.id is a column you tried to select, but did not group. I think you probably want to add this column to the GROUP BY clause.

SELECT 
   invoices.id, 
   sum(grossTotal)-IFNULL(depositsCheck.previouslyPaid,0) as todayTotal, 
   depositsCheck.previouslyPaid, sum(grossTotal) as grossTotal  
FROM `invoices`    
    LEFT JOIN (SELECT SUM(amount) as previouslyPaid, invNo 
    FROM deposits 
    GROUP BY invNo) depositsCheck ON depositsCheck.invNo=invoices.id 
GROUP BY  invoices.paymentType, invoices.id ORDER BY id DESC

For the example tables you gave, it will probably give:

id    |    paymentType     |     grossTotal   |    dateTime   |   previouslyPaid
1     |         Cash       |        1000      |   UNIX TIME   |       150
2     |         Card       |        1350      |   UNIX TIME   |       350
3     |         Card       |        1250      |   UNIX TIME   |         0
4     |         Card       |        750       |   UNIX TIME   |         0

But in general, you will have something like:

id    |    paymentType     |     grossTotal   |    dateTime   |   previouslyPaid
1     |         Cash       |        1000      |   UNIX TIME   |       150
1     |         Card       |        1000      |   UNIX TIME   |       300
2     |         Cash       |        1350      |   UNIX TIME   |       350
2     |         Card       |        1350      |   UNIX TIME   |       350

Where you can see above, for invoice 1, 150 was paid in cash, and 300 was paid by card.


Need Your Help

Reload page in metro app C#

c# xaml windows-8 windows-runtime

I'm developing metro app using Windows 8 RTM and C#(VS 2012 RTM), I'm stuck with page reload,

Web application to user instant messaging

messaging

What options are available for receiving instant alerts from web applications?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.