0

Does the query shown below work? (Sorry for this type of question, but I am a novice and it is not possible for me to run it right now).

SELECT
   table.id 
   SUM(10/ta.value) AS amount 
JOIN tableA ta 
         ON ta.key = t.key

What I am curious about if it is possible to refer to a alias "before" (i.e higher up) it has been assigned.

Thanks!

MDCCL
  • 8,530
  • 3
  • 32
  • 63
Erik P
  • 9
  • 2

2 Answers2

4

FROM is - logically - evaluated before SELECT, so despite the textual order of your query, ta is known to SUM.

There are however a number of things in your query that won't work:

  1. You need a FROM clause
  2. A JOIN is performed between 2 tables

You can probably rewrite your query to:

SELECT table.id 
     , SUM(10/ta.value) AS amount 
FROM tableA ta
WHERE ...

but perhaps this is what you meant?

SELECT t.id 
     , SUM(10/ta.value) AS amount 
FROM ... as t
JOIN tableA ta 
     ON ta.key = t.key
WHERE ta.key = t.key
GROUP BY t.id

Note that you need a GROUP BY for the latter query

The link provided by Jim Horn below is a good start towards a better understanding, so I'll just add a few lines on a methodology for constructing a query.

Step one, figure out what relations that are involved and how the relate. Example:

FROM customers as c
JOIN orders as o 
    ON c.custno = o.custno

SQL is closed under operations like JOIN, UNION and so forth, meaning that the result is a new relation. It is normally not given a name, but in this case, we can think of it as customer_orders

Step two, what customer_orders are we interested in? 

FROM customers as c
JOIN orders as o 
    ON c.custno = o.custno
WHERE year(order_date) = 2019

What details of these customer_orders are we interested in? In this example, I'll assume that we want to know how many orders each customer have:

SELECT c.custno, count(o.orderno)
FROM customers as c
JOIN orders as o 
    ON c.custno = o.custno
WHERE year(order_date) = 2019
GROUP BY c.custno

It is not valid to refer to an aggregate function in the where clause, since the where clause is evaluated before the aggregation (count(...)), but there is a similar construction that can be used. Say that we want to know which customers that have more than 20 orders:

SELECT c.custno, count(o.orderno)
FROM customers as c
JOIN orders as o 
    ON c.custno = o.custno
WHERE year(order_date) = 2019
GROUP BY c.custno
HAVING count(o.orderno) > 20

This is the same as:

SELECT custno, cnt
FROM (
    SELECT c.custno, count(o.orderno) as num_orders
    FROM customers as c
    JOIN orders as o 
        ON c.custno = o.custno
    WHERE year(order_date) = 2019
    GROUP BY c.custno
) as customer_orders
WHERE num_orders > 20

Finally, you may want to retrieve the rows in a particular order. 

SELECT c.custno, count(o.orderno)
FROM customers as c
JOIN orders as o 
    ON c.custno = o.custno
WHERE year(order_date) = 2019
GROUP BY c.custno
HAVING count(o.orderno) > 20
ORDER BY count(o.orderno) desc -- most orders first

Up until the last step the result is a relation, but the order by invalidates the fundamental property that a relation is unordered. It may seem like an irrelevant note, but it is much easier for the DBMS in question to optimize a query if it does not have to take the order of rows in consideration.

Lennart - Slava Ukraini
  • 23,842
  • 3
  • 34
  • 72
1

Lennart's answer is correct. To make things a little clearer for you I'll ask that you change your definition of 'before' from this..

SWEATY (SELECT)
FEET (FROM, with JOINs)
WILL (WHERE)
GIVE (GROUP BY)
HORRIBLE (HAVING)
ODORS (ORDER BY)  (Good luck getting that out of your head)

... to follow the Query Order of Execution (link) which is how the query is processed, and is a different order then how it is typed.

Looking at the link, any alias defined in a section can be used by any section below it.

Jim Horn
  • 121
  • 3