0

Suppose we have summary table called summary with the following fields: date , user_id , song_id and count . It shows at the end of each day how many times in her history a user has listened to a given song.

A second table called daily records whether a user listens to a particular song. This table is used to update the first table. If a song is in the daily table but not in the summary table, then a new record is created in the summary table with the appropriate counts.

Question/Problem I am looking through some sample solutions and was not sure about the particular notation in this answer:

SELECT A.USERID, A.SONG, SUM(A.COUNT) COUNT
FROM
(SELECT USERID, SONG, COUNT FROM SUMMARY
UNION ALL
SELECT USERID, SONG, COUNT FROM DAILY WHERE DATE = NOW()) A
GROUP BY A.USERID, A.SONG

Why is there a COUNT beside A.COUNT in the following line: SUM(A.COUNT) COUNT ? Likewise, where is there an A after DATE = NOW()) in the following line: WHERE DATE = NOW()) A ?

NebulousReveal
  • 562
  • 2
  • 7
  • 19
  • Those are _aliases_. They give names to columns or subqueries so they can be referenced in other places. – D Stanley Aug 07 '17 at 14:19
  • It's an alias. In the case of the column, it's naming the `SUM(A.COUNT)` column `COUNT`, and in the case of the `A`, it's aliasing the sub-query to `A` to be referenced by `A.ColumnName`. – Siyual Aug 07 '17 at 14:20

2 Answers2

0

It's an alias. The example is aliasing a table or field name to something else (in this case, A).

Official MySQL documentation will help you here: https://dev.mysql.com/doc/refman/5.7/en/select.html

Quoting (emphasis added by me):

A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. For example:

SELECT CONCAT(last_name, ', ', first_name) AS full_name FROM mytable ORDER BY full_name;

The AS keyword is optional when aliasing a select_expr with an identifier. The preceding example could have been written like this:

SELECT CONCAT(last_name, ', ', first_name) full_name FROM mytable ORDER BY full_name;

wally
  • 3,492
  • 25
  • 31
  • Doh - assuming it's MySQL you're using! See the appropriate *SQL documentation if you're not - but it'll be the same thing :-) – wally Aug 07 '17 at 14:21
0

These are known as Aliases and simply rename the column. In the case of the Sum(A.Count) this is required as the output of a function has no column name.

This is equivilent to writing

SUM(A.Count) AS Count,

Or 

SUM(A.Count) "Count",
Milney
  • 6,253
  • 2
  • 19
  • 33