Ian C February 2016

Select where with a select?

I have a query finding all the people who have an outstanding balance not equal to 0. I though it was all ok but having expanded the sample data available i have discover that its not quite there (not far off i think).

The code is below:

    SELECT bookingRef, leadname, departureDate, balanceDueDate,
      totalDue, totalReceived, (totalDue - totalReceived) AS outstandingBalance,
      (SELECT SUM(CASE WHEN(totalDue - totalReceived) <> 0 THEN 1 ELSE 0 END)
       FROM bookings) AS numberOutstanding,
      (SELECT SUM(totalDue) FROM bookings) AS grossDue,
      (SELECT SUM(totalReceived) FROM bookings) AS grossReceived,
      (SELECT SUM(totalDue - totalReceived) FROM bookings) AS grossOutstanding
    FROM bookings
    WHERE (amendedDate BETWEEN '2015-09-30' AND '2016-09-30')
    HAVING outstandingBalance <> 0;

What's happening is when the date is across the complete range the results are correct. However when doing just a year as above it still sums the total range, ignoring the where clause for the sums.

I think i need to add the where clause to each sum select but keep running into run errors.

How would i achieve this.

Many thanks

NOTE: Very rusty with MySQL (8-10yrs without using it).

Answers


Lukas Eder February 2016

A solution without window functions (for MySQL)

Unfortunately, MySQL doesn't support window functions, which would be much more convenient for this query. Instead, you can use a cross join in your case. You won't get around applying the predicates twice, though, as MySQL also doesn't support common table expressions:

SELECT b.bookingRef, b.leadname, b.departureDate, b.balanceDueDate,
  b.totalDue, b.totalReceived, (b.totalDue - b.totalReceived) AS outstandingBalance, a.*
FROM bookings b,
CROSS JOIN (
  SELECT 
    SUM(CASE WHEN(totalDue - totalReceived) <> 0 THEN 1 ELSE 0 END AS numberOutstanding,
    SUM(totalDue) AS grossDue,
    SUM(totalREceived) AS grossReceived,
    SUM(totalDue - totalReceived) AS grossOutstanding
  FROM bookings
  WHERE (amendedDate BETWEEN '2015-09-30' AND '2016-09-30')
  AND (totalDue - totalReceived) <> 0
) a
WHERE (b.amendedDate BETWEEN '2015-09-30' AND '2016-09-30')
AND (b.totalDue - b.totalReceived) <> 0;

Alternatively, you could have also duplicated the predicate into each of your subqueries in the SELECT clause, but that approach will probably perform much worse.

A solution with window functions

For completeness' sake, in most other databases, you would be writing this query instead:

SELECT bookingRef, leadname, departureDate, balanceDueDate,
  totalDue, totalReceived, (totalDue - totalReceived) AS outstandingBalance,
  SUM(CASE WHEN(totalDue - totalReceived) <> 0 THEN 1 ELSE 0 END) OVER() AS numberOutstanding,
  SUM(totalDue) OVER() AS grossDue,
  SUM(totalReceived) OVER() AS grossReceived,
  SUM(totalDue - totalReceived) OVER() AS grossOutstanding
FROM bookings
WHERE (amendedDate BETWEEN '2015-09-30' AND '2016-09-30')
AND (b.totalDue - b.totalReceived) <> 0;

A side-note on HAVING

I suspect you're using the HAVING clause in order to be able to re-use a colum

Post Status

Asked in February 2016
Viewed 1,809 times
Voted 13
Answered 1 times

Search




Leave an answer