I need to query a table for some amounts Billed, Received, Adjusted but need to join into the table also a count of the samples that where serviced
I could request this separately, but I would like to have the result nicely in one array
This is the query that I thought would work
SELECT ROUND(SUM(`amount_billed`/1000),2) AS `billed`,
ROUND(SUM(`amount_received`/1000),2) AS `received`,
ROUND(SUM(`amount_adjusted`/1000),2) AS `adjustment` FROM `acs`.`billing` AS **`amount`**
INNER JOIN (SELECT COUNT(`accession_id`) AS `samples` FROM `acs`.`billing` AS **`count`**
WHERE year(`count`.`date_billed`) = 2012 AND year(`count`.`date_paid`) = 2012
HAVING COUNT(`accession_id`) > 1) WHERE year(`amount`.`date_billed`) = 2012 AND year(`amount`.`date_paid`) = 2012;
but MySQL returns the error:
Error Code: 1248. Every derived table must have its own alias
I gave all tables an alias for select query amount and for the join count.
Doing SELECT COUNT(accession_id) AS samples you are assigning an alias to the single field.
You need to assign a alias to the derived table. You actually miss it.
Try to update the query adding at last:
) as table_b;
before your semicolon or where the second query ends (this is not clear reading the query right now because you miss to close the ) that is opened on the inner_join.