Calculations in MySQL using SELECT query?

Question

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)

fthiella · Accepted Answer · 2013-01-30 15:37:06Z

2

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

answered Jan 30, 2013 at 15:37

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cameron Beatty Over a year ago

For the OP's reference, a list of aggregate functions and details about the GROUP BY clause can be found here: dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

echo_Me · Accepted Answer · 2013-01-30 16:01:53Z

0

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

answered Jan 30, 2013 at 16:01

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Collectives™ on Stack Overflow

Calculations in MySQL using SELECT query?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related