-1

I use MariaDB and have two tables (orders, invoiced_positions) and I need to calculate how many of each item were invoiced and paid for. So I need to count the rows and group by item_name. The problem of this is that some of the items are reopened: so I need to subtract the number of reopened invoices from the ones that were not. Basically: I want to do for all items what I achieved here for only one item:

SELECT  
(SELECT COUNT(item_name = 'Streuselkuchen') 
 FROM orders, invoiced_positions 
 WHERE order_id = id AND reopened = 0)
 - (SELECT COUNT(item_name = 'Streuselkuchen') 
    FROM orders, invoiced_positions 
    WHERE order_id = id AND reopened != 0) AS Difference;

I think this can be achieved with a conditional aggregation as is shown here but I haven't been able to apply this to my specific task.

nbk
  • 45,398
  • 8
  • 30
  • 47
PeterStrawson
  • 137
  • 6
  • 2
    Please provide sample data and expected results to clarify your requirement. – GMB Jan 12 '20 at 21:36
  • @PeterStrawson the edits that you propose to my answer would produce invalid sql code. Use the aliases and not the full table names. – forpas Jan 12 '20 at 22:37

1 Answers1

1

You need a proper JOIN of the tables, GROUP BY item_name, and then conditional aggregation:

SELECT item_name, SUM(reopened = 0) - SUM(reopened != 0) AS Difference
FROM orders AS O INNER JOIN invoiced_positions AS IP  
ON IP.order_id = O.id     
GROUP BY item_name;

I did not qualify the columns item_name and reopened with table aliases because it is not obvious where they belong, but you should do it.

PeterStrawson
  • 137
  • 6
forpas
  • 160,666
  • 10
  • 38
  • 76