Alias and subquery

Databases
1

This seems simple, but I can’t quite get it.
Says that allocation_sum in the last line is an unknown column.


SELECT payments.*,
(SELECT sum(allocations.amount) FROM allocations WHERE payments.id = allocations.payment_id) as allocation_sum
FROM payments
WHERE payments.amount <> allocation_sum;
2 
SELECT payments.*
     , totals.sum_allocation
  FROM payments
INNER
  JOIN ( SELECT payment_id
              , SUM(allocations.amount) AS sum_allocation
           FROM allocations
         GROUP
             BY payment_id ) AS totals
    ON totals.payment_id = payments.id
   AND totals.sum_allocation <> payments.amount
3

Thanks!’

Why wouldn’t the other one work? Can you not reference subquery alias’ in the where clause?

4

no, you can’t :slight_smile:

5

Hi, I’m having a similar problem to this chap and i’m trying to work out how to fix it but its making my brain ache horribly, could you put me out my misery and take a swiz at mine? pleeeeease?

SELECT * ,(
SQRT( ABS( POWER( ( Grid_E -255500 ) , 2 ) + POWER( ( Grid_N -385200 ) , 2 ) ) )
) / 1609.344 AS distance
FROM postcodes WHERE distance < 10 ORDER BY distance ASC

this works out the distance between two grid refs and pops them into miles,a the problem is with the where clause, it says unknown column…
i’d really appreciate a bit of help…

6

just wrap a “select star” around your existing query, and move the WHERE clause to the outside…

[COLOR="red"]SELECT *
  FROM ( [/COLOR]SELECT *
              , ( SQRT( ABS( POWER( ( Grid_E -255500 ) , 2 ) +
                             POWER( ( Grid_N -385200 ) , 2 ) ) )
                   ) / 1609.344 AS distance
           FROM `postcodes`
       [COLOR="red"]) AS derivedtable
 WHERE distance &lt; 10
ORDER
    BY distance ASC[/COLOR]

simple, yes? :slight_smile:

7

rudy thats just incredible thank you so much, i’m using a really rubbish sql book at the moment, and i’m putting yours into my basket now!
also noticed you didnt have a wishlist?

8

thanks for the kind words :slight_smile:

no, no wish list – at least, not one which i’d post on teh interwebs for all and sundry to laugh at

:smiley:

9

maybe you should post a wishlist and then people can say thankyou when you save them hours if not days of work and frustration, you know i have about eight books on mysql and not one of them shows me how to do what you just have? i think its awesome that you have such skills and are so willing to help people, youre an inspiration ;0)

10

well, i just got one of my wishes fulfilled by your last post

:slight_smile: