CODEWITHSUNDEEP
mysqlcountsumunion
Philipp Doublehammer
Philipp Doublehammer

Reputation: 332

SQL query to SUM up tables from SELECT COUNT statement with UNION

I'm currently trying to sum up the result of an MySQL statement which gives me the amount of rows of several different tables.

I already studied every solution of stackoverflow concerning this matter. Unfortunately I'm not able to figure out the correct syntax for my case.

My working statement with the results not summed up:

SET @shipmentId=456;
SELECT
        (SELECT COUNT(*) FROM Table1 WHERE ShipmentID = @shipmentId)
        UNION ALL
        (SELECT COUNT(*) FROM Table2 WHERE ShipmentID = @shipmentId)
        UNION ALL
        (SELECT COUNT(*) FROM Table3 WHERE ShipmentID = @shipmentId)
        UNION ALL
        (SELECT COUNT(*)FROM Table4 WHERE ShipmentID = @shipmentId)
        UNION ALL
        (SELECT COUNT(*)FROM Table5 WHERE ShipmentID = @shipmentId);

In my opinion this should be the nearest to a correct solution but I'm not sure (tried a lot of variants):

SET @shipmentId=456;
SELECT SUM(MeasuredValues) FROM
    SELECT(
        (SELECT COUNT(*) FROM Table1 WHERE ShipmentID = @shipmentId) AS MeasuredValues
        UNION ALL
        (SELECT COUNT(*) FROM Table2 WHERE ShipmentID = @shipmentId) AS MeasuredValues
        UNION ALL
        (SELECT COUNT(*) FROM Table3 WHERE ShipmentID = @shipmentId) AS MeasuredValues
        UNION ALL
        (SELECT COUNT(*)FROM Table4 WHERE ShipmentID = @shipmentId) AS MeasuredValues
        UNION ALL
        (SELECT COUNT(*)FROM Table5 WHERE ShipmentID = @shipmentId)  AS MeasuredValues) t1;

Thanks in advance for your help.

Already tried the following links: StackOverFlow Solution1

StackOverFlow Solution2

Upvotes: 0

Views: 96

Answers (1)

Kosh
Kosh

Reputation: 18378

You could just simply sum up your selects:

SET @shipmentId=456;
SELECT
  (SELECT COUNT(*) FROM Table1 WHERE ShipmentID = @shipmentId)
+
  (SELECT COUNT(*) FROM Table2 WHERE ShipmentID = @shipmentId)
+
  (SELECT COUNT(*) FROM Table3 WHERE ShipmentID = @shipmentId)
+
  (SELECT COUNT(*) FROM Table4 WHERE ShipmentID = @shipmentId)
+
  (SELECT COUNT(*) FROM Table5 WHERE ShipmentID = @shipmentId);

Upvotes: 1

Related Questions