0

I have data like this

+----+-----+------------+-------------+--------------+----------------+------------+-------+
| id | seq | allocation | template_id | headcount_id | cost_center_id | country_id | total |
+----+-----+------------+-------------+--------------+----------------+------------+-------+
|  1 |   1 |       1.00 |           1 |            1 |           1234 |          1 |    10 |
|  2 |   1 |       1.00 |           1 |            1 |           1234 |          1 |    12 |
|  3 |   1 |       1.00 |           1 |            1 |           1234 |          1 |     5 |
|  4 |   1 |       1.00 |           1 |            1 |           1234 |          1 |     8 |
|  5 |   1 |       1.00 |           1 |            1 |           1234 |          1 |     4 |
|  6 |   1 |       1.00 |           1 |            1 |           1234 |          1 |    12 |
|  7 |   1 |       1.00 |           1 |            1 |           1234 |          1 |     2 |
|  8 |   1 |       1.00 |           1 |            1 |           1234 |          1 |    13 |
|  9 |   2 |       0.50 |           1 |            2 |           6578 |          2 |    41 |
| 10 |   2 |       0.50 |           1 |            2 |           6578 |          2 |    13 |
| 11 |   2 |       0.50 |           1 |            2 |           6578 |          2 |   200 |
| 12 |   2 |       0.50 |           1 |            2 |           6578 |          2 |    14 |
| 13 |   2 |       0.50 |           1 |            2 |           6578 |          2 |   478 |
| 14 |   2 |       0.50 |           1 |            2 |           6578 |          2 |   658 |
| 15 |   2 |       0.50 |           1 |            2 |           6578 |          2 |   589 |
+----+-----+------------+-------------+--------------+----------------+------------+-------+

Image: Link

I need to get the total of total by country and sum of allocation distinct by seq.

Output would be like

+------------+------------+-------+
| allocation | country_id | total |
+------------+------------+-------+
|          1 |          1 |     66|
|       0.50 |          2 |   1993|
+------------+------------+-------+
4
  • Annual DC is the Total of Total. – Julius Yerro Aug 13 '19 at 7:26
  • Annual is the SUM of TOTAL by countries – Julius Yerro Aug 13 '19 at 7:36
  • Is that a single input table? You seem to have 6 columns that are correlated to each other. – Rick James Aug 13 '19 at 13:57
  • 1
    I think that @DxTx (+1) gave you a great answer. There are a few articles about how to ask questions here on my profile - you might want to take a look? You appear to be shifting the goalposts in terms of your desired answer - what more could DxTx have done? p.s. welcome to the forum! :-) – Vérace Aug 13 '19 at 17:49
1

Something like this..?

Table structure and sample data

CREATE TABLE sample (
  id int, 
  seq int, 
  allocation FLOAT(10, 2), 
  template_id int, 
  headcount_id int, 
  cost_center_id int, 
  country_id int, 
  total int
);
INSERT INTO sample 
VALUES 
  (1, 1, '1.00', 1, 1, 1234, 1, 10), 
  (2, 1, '1.00', 1, 1, 1234, 1, 12), 
  (3, 1, '1.00', 1, 1, 1234, 1, 5), 
  (4, 1, '1.00', 1, 1, 1234, 1, 8), 
  (5, 1, '1.00', 1, 1, 1234, 1, 4), 
  (6, 1, '1.00', 1, 1, 1234, 1, 12), 
  (7, 1, '1.00', 1, 1, 1234, 1, 2), 
  (8, 1, '1.00', 1, 1, 1234, 1, 13), 
  (9, 2, '0.50', 1, 2, 6578, 2, 41), 
  (10, 2, '0.50', 1, 2, 6578, 2, 13), 
  (11, 2, '0.50', 1, 2, 6578, 2, 200), 
  (12, 2, '0.50', 1, 2, 6578, 2, 14), 
  (13, 2, '0.50', 1, 2, 6578, 2, 478), 
  (14, 2, '0.50', 1, 2, 6578, 2, 658), 
  (15, 2, '0.50', 1, 2, 6578, 2, 589);

Query

SELECT 
  Max(allocation) as allocation, 
  country_id, 
  Sum(total) as total 
FROM 
  sample 
GROUP BY 
  country_id

Output

+-------------+-------------+-------+
| allocation  | country_id  | total |
+-------------+-------------+-------+
|          1  |          1  |    66 |
|        0.5  |          2  |  1993 |
+-------------+-------------+-------+

Online Demo: http://sqlfiddle.com/#!9/547260/3/0

8
  • The end result should be group by countries while getting the sum of totals by countries and sum of allocation with distinct seq by countries. – Julius Yerro Aug 13 '19 at 7:28
  • it works, but it does not add up the allocation where the seq should be distinct. – Julius Yerro Aug 13 '19 at 8:00
  • sqlfiddle.com/#!9/08ce954/1 – Julius Yerro Aug 13 '19 at 8:01
  • i need the allocation added to other row with the same country id – Julius Yerro Aug 13 '19 at 8:06
  • @JuliusYerro EACH allocation in your sample mathes THE SAME country id. So your phrase makes no sense. – Akina Aug 13 '19 at 8:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.