SQL: How do I add different columns from different tables and make it one amount?-CodePudding

I am trying to add columns from different tables in order to get a total amount of all my streams. I've tried joining SQL JOIN them which works fine but it doesn't allow me to get the total number of streams. I've now decided to use union SQL UNION all but I still can't get the total number of streams. How do I go about doing getting a final amount after concatenating these tables. the solution that I got from previous questions here on stack overflow don't seem to work as well.

from http.client import OK
from logging import exception
import psycopg
import pandas as pd

dbname = '******' 
user ='******'
password = '*******' 
host = '**********' 
port = **** 
conn = None

try:
    with psycopg.connect(
        dbname = dbname,
        user = user,
        password = password,
        host = host, 
        port = port 
    ) as conn:

        with conn.cursor() as cur:

            total_streaming_plays = '''SELECT artist, SUM(plays) AS total_plays
            FROM SELECT artist, plays FROM machine_spotify
            UNION ALL
            SELECT artist, plays FROM machine_tidal
            UNION ALL
            SELECT artist, plays FROM machine_soundcloud
            UNION ALL
            SELECT artist, plays FROM machine_youtube
            UNION ALL
            SELECT artist, plays FROM machine_youtube_music
            GROUP BY artist;'''

            cur.execute(total_streaming_plays) 
            for records in cur:
                print(records)   

        conn.commit()

except Exception as e:
    print(e)

finally:
    if conn is not None:
        conn.close()

output

syntax error at or near "SELECT"
LINE 2:             FROM SELECT artist, plays FROM machine_spotify

CodePudding user response：

I think you need a ()

SELECT artist, SUM(plays) AS total_plays
            FROM (SELECT artist, plays FROM machine_spotify)
            UNION ALL
            (SELECT artist, plays FROM machine_tidal)
            UNION ALL
            (SELECT artist, plays FROM machine_soundcloud)
            UNION ALL
            (SELECT artist, plays FROM machine_youtube)
            UNION ALL
            (SELECT artist, plays FROM machine_youtube_music)
            GROUP BY artist;

I think this will address the error, but I don't think it's a good practice, I would prefer using With AS .

WITH temp AS (
    SELECT artist, plays FROM machine_spotify
            UNION ALL
            (SELECT artist, plays FROM machine_tidal)
            UNION ALL
            (SELECT artist, plays FROM machine_soundcloud)
            UNION ALL
            (SELECT artist, plays FROM machine_youtube)
            UNION ALL
            (SELECT artist, plays FROM machine_youtube_music)
)
SELECT artist, SUM(plays) AS total_plays
FROM temp

All in all, it is too hard to give suggestion without sample data.