Mysql - Get the sum of all the different rows based on other values

Question

I have a table that looks like this:

+-----+--------+-------+
| num | amount | value |
+-----+--------+-------+
|   1 |     12 |     1 |
|   1 |     12 |     1 |
|   2 |     13 |     1 |
|   4 |     15 |     0 |
|   2 |     13 |     1 |
|   3 |     14 |     1 |
|   3 |     14 |     1 |
|   1 |     12 |     1 |
+-----+--------+-------+

I want to sum the “sum” column based on a separate “num” column, where the “value” is 1, for example, after executing the following query,

select DISTINCT num, amount, value from test where value =1 ;

great num based table,

+-----+--------+-------+
| num | amount | value |
+-----+--------+-------+
|   1 |     12 |     1 |
|   2 |     13 |     1 |
|   3 |     14 |     1 |
+-----+--------+-------+

so I want the end result to be

12 + 13 + 14 = 39.

one more thing:

I can not use the subquery.

because it is already part of another request.

here is the script of my table

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `test`
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `num` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `value` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', '12', '1');
INSERT INTO `test` VALUES ('1', '12', '1');
INSERT INTO `test` VALUES ('2', '13', '1');
INSERT INTO `test` VALUES ('4', '15', '0');
INSERT INTO `test` VALUES ('2', '13', '1');
INSERT INTO `test` VALUES ('3', '14', '1');
INSERT INTO `test` VALUES ('3', '14', '1');
INSERT INTO `test` VALUES ('1', '12', '1');

Answer 1

 select num,sum(amount)/Count(*)
 from test
 where value = 1
 group by num
 order by num

Answer 2

I assume that each number has the same value (if not, how to choose it?)

so this will work:

SELECT SUM(DISTINCT amount)
FROM test
WHERE value = 1

Answer 3

SELECT SUM(amount) FROM  (SELECT DISTINCT num, amount, VALUE FROM test WHERE VALUE =1) AS temp  ;

Answer 4

, , SUM() . :

SELECT SUM(amount) 
FROM (
       SELECT DISTINCT t.`num`, t.`amount` 
       FROM test t 
       WHERE t.`value`=1
     ) temp

Answer 5

Check solution

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `test`
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `num` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `value` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', '12', '1');
INSERT INTO `test` VALUES ('1', '12', '1');
INSERT INTO `test` VALUES ('2', '13', '1');
INSERT INTO `test` VALUES ('4', '15', '0');
INSERT INTO `test` VALUES ('2', '13', '1');
INSERT INTO `test` VALUES ('3', '14', '1');
INSERT INTO `test` VALUES ('3', '14', '1');
INSERT INTO `test` VALUES ('1', '12', '1');

-- ----------------------------
-- Insert the result set into temp table and then use sum().
-- ----------------------------


SELECT DISTINCT `num` , `amount` 
INTO #tmp
FROM test 
WHERE `value`   =1

SELECT * FROM #tmp

SELECT sum(`amount`) as amount FROM #tmp