CODEWITHSUNDEEP
mysqlselect
Nayana Rajapaksha
Nayana Rajapaksha

Reputation: 161

Calculations in MySQL using SELECT query?

what you see below is the stock table I made for my software. and I need to do many calculations using it. for example I need the sum of pidstk P000007 (qty) where type is 3,1 and type 2,4 then I want the answer of (type 3+1) - (type 2+4). how can I do this? sorry about my English. not really good at it.

+--------+------------+-----+------+-----------------+
| pidstk | dateup     | qty | type | refno           |
+--------+------------+-----+------+-----------------+
| P00007 | 2013-01-31 |  50 |    4 | 000000000000004 |
| P00007 | 2013-01-03 |  24 |    4 | 000000000000005 |
| P00007 | 2013-01-22 |  40 |    4 | 000000000000006 |
| P00007 | 2013-01-22 |  40 |    4 | 000000000000007 |
| P00007 | 2013-01-22 |  14 |    4 | ref             |
| P00007 | 2013-01-22 |   8 |    1 | ref             |
| P00007 | 2013-01-22 |   3 |    2 | ref             |
| P00007 | 2013-01-22 |   2 |    3 | ref             |
| P00007 | 2013-01-22 |   1 |    3 | ref             |
| P00007 | 2013-01-23 |  10 |    4 | 000000000000008 |
| P00007 | 2013-01-23 |   2 |    4 | 000000000000008 |
| P00007 | 2013-01-23 |  40 |    4 | 000000000000008 |
| P00007 | 2013-01-23 |  40 |    4 | 000000000000008 |
| P00007 | 2013-01-24 |   1 |    4 | 000000000000009 |
| P00007 | 2013-01-31 |  40 |    4 | 000000000000010 |
+--------+------------+-----+------+-----------------+
15 rows in set (0.00 sec)

Upvotes: 1

Views: 648

Answers (2)

echo_Me
echo_Me

Reputation: 37233

try this

   SELECT
   pidstk,
  sum(case when type in (1,4) then qty else 0 end) as total_1_4 ,
  sum(case when type in (2,3) then qty else 0 end) as total_2_3,
  sum(case when type in (1,4) then qty else 0 end)- sum(case when type in (2,3) then qty else 0 end) as the_difference
  FROM stock
  GROUP BY pidstk

DEMO SQLFIDDLE

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You could use something like this:

SELECT
  pidstk,
  sum(case when type in (1,4) then qty else 0 end)-
  sum(case when type in (2,3) then qty else 0 end)
FROM
  yourtable
GROUP BY
  pidstk

Upvotes: 2

Related Questions