SQL SELECT avg of columns from multiple tables

user_11615469 0
Viewed 47

I have a SQL query now that produces a table that looks like this:

Demographic Month Week Usability Session
2 10 5 7 9

This is the query:

SELECT

    avg(cast(d.duration as bigint)) as "Demographic",
    avg(cast(m.duration as bigint)) as "Month",
    avg(cast(w.duration as bigint)) as "Week",
    avg(cast(u.duration as bigint)) as "Usability",
    avg(cast(s.duration as bigint)) as "Session"
   
FROM 
    "demographic" as d,
    "month" as m,
    "week" as w,
    "usability" as u,
    "session" as s

What I want is a table that looks like this:

Table Average Duration
Demographic 2
Month 10
Week 5
Usability 7
Session 9

Is this possible?

amazon-quicksight mysql sql
1 Answers

Use UNION to get each result on a different row.

SELECT 'Demographic' AS Table, AVG(CAST(duration) AS BIGINT) AS `Average Duration` FROM demographic
UNION ALL
SELECT 'Month', AVG(CAST(duration) AS BIGINT) FROM month
UNION ALL
SELECT 'Week', AVG(CAST(duration) AS BIGINT) FROM week
UNION ALL
SELECT 'Usability', AVG(CAST(duration) AS BIGINT) FROM usability
UNION ALL
SELECT 'Session', AVG(CAST(duration) AS BIGINT) FROM session
user_14918950
Related