CODEWITHSUNDEEP
mysqlsql
Wells
Wells

Reputation: 142

How to sum and count Id's of different tables from a Union

I have 3 tables councils, station_levy, market_levy, and the station table is joined to get councils in station_levy. I need to get data by council sum the number of station_levy + market_levy and get the total amount tendered.

Tables are as follows

councils
---------------+----------+
| council_id   | Name     |
+--------------+----------+
| 1            | LSK      |
---------------+----------+
| 2            | KBW      |
---------------+----------+


station_levy
------------------+-------------+-----------------+
| station_levy_id | station_id  | amount_tendered |
+-----------------+-------------+-----------------+
|       1         |   3         |    10.00        |
+-----------------+-------------+-----------------+
|       2         |   3         |    10.00        |
+-----------------+-------------+-----------------+
|       3         |   1         |    5.00         |
+-----------------+-------------+-----------------+


(station_id = 1 is found in the LSK council_id=1  And station_id = 3 is found in the KBW council_id=2)

market_levy
------------------+-------------+-----------------+
| market_levy_id  | council_id  | amount_tendered |
+-----------------+-------------+-----------------+
|       1         |   1         |    5.00         |
+-----------------+-------------+-----------------+
|       2         |   2         |    5.00         |
+-----------------+-------------+-----------------+
|       3         |   1         |    5.00         |
+-----------------+-------------+-----------------+

mysql

SELECT c.council_name, (COUNT(market_levy.market_levy_id)+ COUNT(st.station_levy_id )) count, SUM(amount_tendered) revenue
    FROM councils c 
    JOIN (
        (SELECT council_id, amount_tendered,market_levy_id  FROM market_levy  WHERE transaction_date >= CURDATE() )
        UNION ALL

        (SELECT station_levy_id , councils.council_id, amount_tendered 
        FROM station_levy st     
        JOIN stations ON stations.station_id = st.station_id
        JOIN councils ON councils .council_id= stations .council_id
        WHERE transaction_datetime >= CURDATE()
    )) totalCouncilRevenue USING (council_id)
    group by council_id, c.council_name ORDER BY SUM(amount_tendered) DESC

Expected result

------------------+-------------+-----------------+
| council_name    | count       | revenue         |
+-----------------+-------------+-----------------+
|      LSK        |   3         |    15.00        |
+-----------------+-------------+-----------------+
|      KBW        |   3         |    25.00        |
+-----------------+-------------+-----------------+

Upvotes: 0

Views: 46

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You are confusing columns in your UNION ALL matching council_id with station_levy_id, amount_tendered with council_id, and market_levy_id with amount_tendered.

Then, in your main query you try to access market_levy.market_levy_id and st.station_levy_id, but these columns are not accessible, as you select from a subquery called totalCouncilRevenue, not from tables labelled market_levy and st there.

Your query fixed:

SELECT 
  c.council_name, 
  COUNT(*) AS transaction_count,
  SUM(amount_tendered) AS revenue
FROM councils c 
JOIN
(
  SELECT council_id, amount_tendered
  FROM market_levy  
  WHERE transaction_date >= CURDATE() 
  UNION ALL
  SELECT s.council_id, st.amount_tendered 
  FROM station_levy st    
  JOIN stations s ON s.station_id = st.station_id
  WHERE st.transaction_datetime >= CURDATE()
) totalCouncilRevenue USING (council_id)
GROUP BY council_id, c.council_name 
ORDER BY SUM(amount_tendered) DESC;

I prefer aggregating before joining, though:

SELECT 
  c.council_name, 
  COALESCE(t1.cnt, 0) + COALESCE(t2.cnt, 0) AS transaction_count,
  COALESCE(t1.total, 0) + COALESCE(t2.total, 0) AS revenue
FROM councils c 
LEFT JOIN
(
  SELECT council_id, SUM(amount_tendered) as total, COUNT(*) as cnt
  FROM market_levy  
  WHERE transaction_date >= CURDATE() 
  GROUP BY council_id
) t1 USING (council_id)
LEFT JOIN
(
  SELECT s.council_id, SUM(st.amount_tendered) as total, COUNT(*) as cnt
  FROM station_levy st    
  JOIN stations s ON s.station_id = st.station_id
  WHERE st.transaction_datetime >= CURDATE()
  GROUP BY s.council_id
) t2 USING (council_id)
ORDER BY revenue DESC;

Such queries are usually less prone to errors and are sometimes faster, because they may be able to use indexes more efficiently.

Upvotes: 1

Related Questions