Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to retrieve data from 2 different where?

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

enter image description here

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
Tags:

6

Answers


  1. 0

    You can use IN clause

    SELECT ITEM_ID, SUM(QUANTITY) AS QUANTITY
WHERE TYPE IN [OUT,IN]
GROUP BY ITEM_ID
    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
  4. 0

    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
    Login or Signup to reply.
  5. 0

    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.

    Login or Signup to reply.
  6. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search