How do I select average with multiple join clauses?

Question

I want to get the average number (Attendee NPS) from a SQL table I've already put together.

I've encased the initial table in a new select statement so I can take the average of distinct values. Is there something in my Join clause that is preventing this from working?

Im getting the following error:

ERROR: missing FROM-clause entry for table "gizmo" Position: 12

SELECT
    avg(bigtable.gizmo.attendee_nps)
FROM
    (
        SELECT DISTINCT 
            attendee_survey_results.swoop_event_id AS "Swoop ID",
            attendee_survey_results.startup_weekend_city AS "SW City",
            swooptable.start_date AS "Date",
            gizmo.attendee_nps AS "Attendee NPS"
        FROM attendee_survey_results
        JOIN 
            ( 
                SELECT
                    swoop_event_id,
                    (
                        100 * count(CASE WHEN attendee_nps >= 9 THEN 1 END)
                         / count(attendee_nps)
                         - 100 * count(CASE WHEN attendee_nps <= 6 THEN 1 END)
                         / count(attendee_nps)
                    ) AS "attendee_nps"
                FROM attendee_survey_results
                GROUP BY swoop_event_id
            ) AS "gizmo"
            ON gizmo.swoop_event_id = attendee_survey_results.swoop_event_id

        JOIN 
            (
                SELECT eid,start_date,manager_email
                FROM events
            ) AS "swooptable"
            ON gizmo.swoop_event_id = swooptable.eid
    ) AS bigtable

Answer 1

[edit, ok you don't have a single problem, but the request at the bottom should work]

3 part notation bigtable.gizmo.attendee_nps

You can't use this bigtable.gizmo.attendee_nps, this is the "with DB" specific syntax : db_name.tbl_name.col_name.

You should use a table_or_alias.col_name_or_alias notation

In sub query you loose the deep table name of every deep-1 :

    -- with the deep explicite
SELECT `d0`.`new_field`
FROM (
        -- implicite `d1` table 
    SELECT `new_field`
    FROM (
            -- with the deep `d2` explicite and alias of field
        SELECT `d2`.`field` AS `new_field`
        FROM (
                -- without the explicite `d3` table and `field` field
            SELECT *
            FROM (
                    -- output a `field` => 12
                SELECT 12 as `field`
            ) AS `d3`
        ) AS `d2`
    ) AS `d1`
) AS `d0`

    -- print `new_field` => 12

Access deep-1 aliased field

SELECT `attendee_nps`
FROM
    (
        SELECT `attendee_nps` AS `new_alias_field`
        FROM attendee_survey_results
    ) AS bigtable

Unknown column 'attendee_nps' in 'field list'

When you make a field alias in deep-1 query, deep-0 can only access the alias new_alias_field, the original field no longer exist.

Double quote " table alias

FROM (
        -- ...
    ) AS "bigtable"

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"bigtable"' at line N

MySql don't allow the use of " to make table alias (it's technically ok for field alias).

You should use the mysql back quote to escape table alias name, like AS `My Table Alias`

---

Correct query :

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE events
    (`eid` int, `start_date` varchar(10), `manager_email` varchar(15))
;

INSERT INTO events
    (`eid`, `start_date`, `manager_email`)
VALUES
    (1, '2016-11-11', 'mail_1@mail.com'),
    (2, '2016-11-12', 'mail_2@mail.com'),
    (3, '2016-11-13', 'mail_3@mail.com'),
    (4, '2016-11-14', 'mail_4@mail.com'),
    (5, '2016-11-15', 'mail_5@mail.com'),
    (6, '2016-11-16', 'mail_6@mail.com'),
    (7, '2016-11-17', 'mail_7@mail.com')
;


CREATE TABLE attendee_survey_results
    (`id` int, `swoop_event_id` int, `startup_weekend_city` varchar(6), `attendee_nps` int)
;

INSERT INTO attendee_survey_results
    (`id`, `swoop_event_id`, `startup_weekend_city`, `attendee_nps`)
VALUES
    (1, 1, 'city_1', 1),
    (2, 2, 'city_2', 22),
    (3, 3, 'city_3', 3),
    (4, 1, 'city_4', 4),
    (5, 2, 'city_5', 5),
    (6, 3, 'city_6', 9),
    (7, 7, 'city_7', 17)
;

Query 1:

SELECT
    AVG(`bigtable`.`attendee_nps`)
FROM
    (
        SELECT DISTINCT 
            `asr`.`swoop_event_id` AS `Swoop ID`,
            `asr`.`startup_weekend_city` AS `SW City`,
            `swooptable`.`start_date` AS `date`,
            `gizmo`.`attendee_nps` AS `attendee_nps`
        FROM `attendee_survey_results` AS `asr`
        JOIN 
            ( 
                SELECT
                    `swoop_event_id`,
                    (
                        100 * count(CASE WHEN `attendee_nps` >= 9 THEN 1 END)
                         / count(`attendee_nps`)
                         - 100 * count(CASE WHEN `attendee_nps` <= 6 THEN 1 END)
                         / count(`attendee_nps`)
                    ) AS `attendee_nps`
                FROM `attendee_survey_results`
                GROUP BY `swoop_event_id`
            ) AS `gizmo`
            ON `gizmo`.`swoop_event_id` = `asr`.`swoop_event_id`

        JOIN 
            (
                SELECT `eid`, `start_date`, `manager_email`
                FROM `events`
            ) AS `swooptable`
            ON `gizmo`.`swoop_event_id` = `swooptable`.`eid`
    ) AS `bigtable`

Results:

| AVG(`bigtable`.`attendee_nps`) |
|--------------------------------|
|                   -14.28571429 |