76168

Using a Join with Grouped Data Rows

Question:

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

Answer1:

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.

Recommend

  • L5 How to use trait that hashes id but keep pivot functionality
  • Unable to Dynamically Change panel
  • How to add custom fields to the order_invoice.tpl, OpenCart 2.0
  • Entity Framework - Saving child entities on update
  • Pandas split array based on condition
  • How to group a list of lists by date using Linq?
  • How to Optimize mach_msg_trap
  • Why Is jQuery Not Submitting This Form? (Or What Have I Done Wrong? :-p)
  • Send money from Braintree
  • Eloquent Relationship on Partial Match
  • Insertion large number of Entities into SQL Server 2012 [duplicate]
  • EntLib Way to Bind “Null” Value to Parameter
  • Time out Error in send mail
  • ApplePay PKPaymentAuthorizationViewController always shows processing
  • Redirect to Post Method/Action
  • Linq Merge lists
  • Cast between interfaces whose interface signatures are same
  • Django model inheritance, filtering models
  • Unable to install Git-core+svn by MacPorts
  • Django simple Captcha “No module named fields” error
  • Could not find rake using whenever rails
  • C++ Partial template specialization - design simplification
  • Javascript simulate pressing enter in input box
  • Is possible to count alias result on mysql
  • angularjs unit test when to use $rootScope.$new()
  • vba code to select only visible cells in specific column except heading
  • Do I've to free mysql result after storing it?
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • Transpose CSV data with awk (pivot transformation)
  • Comma separated Values
  • Benchmarking RAM performance - UWP and C#
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • Trying to get generic when generic is not available
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • Turn off referential integrity in Derby? is it possible?
  • Add sale price programmatically to product variations
  • Sorting a 2D array using the second column C++
  • Unable to use reactive element in my shiny app
  • How do I use LINQ to get all the Items that have a particular SubItem?