How to retrieve data from 2 different where?-CodePudding

I don't know how to word the title properly, anyway what I mean is: For example I have this 'transaction' table

SELECT ITEM_ID, SUM(QUANTITY) AS IN
WHERE TYPE = IN
GROUP BY ITEM_ID
-
SELECT ITEM_ID, SUM(QUANTITY) AS OUT
WHERE TYPE = OUT
GROUP BY ITEM_ID

How do I combine those 2 statements so I can do operation on them? Thanks Basically what I want is something like this

SELECT ITEM_ID, 
SUM(QUANTITY) where Type = In "Item In", 
SUM(Quantity) where Type = Out "Item Out", 
Item In - Item Out "Final Qty"
GROUP BY ITEM_ID

CodePudding user response：

You can use IN clause

SELECT ITEM_ID, SUM(QUANTITY) AS QUANTITY
WHERE TYPE IN [OUT,IN]
GROUP BY ITEM_ID

CodePudding user response：

You want conditional aggregation, i.e. case expressions in the aggregation function:

SELECT
  item_id,
  SUM(quantity) AS total,
  SUM(CASE WHEN type = 'IN' THEN quantity ELSE 0 END) AS in,
  SUM(CASE WHEN type = 'OUT' THEN quantity ELSE 0 END) AS out
FROM mytable
GROUP BY item_id
ORDER BY item_id;

Another option would be to join the query results:

SELECT *
FROM ( <first query here> ) q1
JOIN ( <second query here> ) q2 USING (item_id);

CodePudding user response：

You can do as using the below queries with OR condition for type / You can use IN condition to get in a single result. Both will do the same work, just the query condition is different.

Using OR condition:

  SELECT ITEM_ID, TYPE , SUM(QUANTITY) AS TYPE_SUM

  WHERE TYPE = "OUT" OR TYPE = "IN"

  GROUP BY ITEM_ID, TYPE

If we are using the same column then we can use OR condition to fetch the data.

Using IN Condition

  SELECT ITEM_ID,TYPE , SUM(QUANTITY) AS TYPE_SUM

  WHERE TYPE IN ("OUT", "IN")

  GROUP BY ITEM_ID,TYPE

CodePudding user response：

How about this

SELECT 
    ITEM_ID, 
    SUM(CASE WHEN TYPE=IN THEN QUANTITY else 0) AS IN_QUANTITY, 
    SUM(CASE WHEN TYPE=OUT THEN QUANTITY else 0) AS OUT_QUANTITY
WHERE TYPE IN [OUT,IN]
GROUP BY ITEM_ID

CodePudding user response：

Another option is to use COUNT and IF

SELECT 
    ITEM_ID, 
    COUNT(IF(TYPE='IN',1,0)) AS IN_QUANTITY, 
    COUNT(IF(TYPE='OUT',1,0)) AS OUT_QUANTITY
WHERE TYPE IN ('OUT','IN')
GROUP BY ITEM_ID

CodePudding user response：

Create a subquery that pivots your rows into columns then SELECT from your subquery to find the difference:

SELECT a.ITEM_ID, 
       a.`Item In`, 
       a.`Item Out`, 
      (a.`Item In` - a.`Item Out`) AS `Final Qty`
FROM
(SELECT 
    ITEM_ID, 
    SUM( IF( TYPE = 'IN', QUANTITY, 0 ) ) AS `Item In`,  
    SUM( IF( TYPE = 'OUT', QUANTITY, 0 ) ) AS `Item Out`
  FROM 
    sample_table 
GROUP BY 
    ITEM_ID) a

Result:

ITEM_ID	Item In	Item Out	Final Qty
X1	10	3	7
X2	12	2	10

db<>fiddle here.