SQL joins two group BY sum

Question

I have two Tables Lossess_tab and numbers_tab. Losses_tab contains the loss of each item.Sum of loss will get the total loss of an item.numbers_tab contains the numbers of each item.Sum of numbers will get the total numbers of an item.

CREATE TABLE IF NOT EXISTS `losses_tab` (`id` bigint(20) unsigned NOT NULL,`item_id` varchar(50) NOT NULL,`loss` varchar(120) NOT NULL);


INSERT INTO `losses_tab` (`id`, `item_id`, `loss`) VALUES (1,100,1.5),(2,100,1.8),(3,102,1.0),(4,103,1.0),(5,101,0),(6,102,1);

CREATE TABLE IF NOT EXISTS `numbers_tab` (
  `item_id` varchar(50) NOT NULL,
  `number` varchar(120) NOT NULL);
  INSERT INTO `numbers_tab` ( `item_id`, `number`) VALUES
    (100,10),(100,12),(102,1),(103,25),(101,16),(103,9),(102,8);

I Want the result as

------------------------------------------------
| item_id  | total loss   | total number  |
------------------------------------------------
| 100      | 3.3          | 22            |
------------------------------------------------
| 101      | 0            | 16            |
------------------------------------------------
| 102      | 2.0          | 9             |
------------------------------------------------
| 103      | 1.0          | 34            |
------------------------------------------------

Here my Fiddle link Link to view the fiddle

TABLE losses_tab

 id item_id loss
    1   100     1.5
    2   100     1.8
    3   102     1.0
    4   103     1.0
    5   101     0
    6   102     1

TABLE numbers_tab

item_id number
100    10
100    12
102    1
103    25
101    16
103    9
102    8

score 1 · Accepted Answer · answered Jun 22 '15 at 10:36

1

Try as

select item_id, sum(loss) as total_loss,
(select sum(number) from numbers_tab nt where nt.item_id = losses_tab.item_id) as total_number
from losses_tab group by item_id

Fiddle

answered Jun 22 '15 at 10:36

Narendrasingh Sisodia

21,247
6
47
54

score 1 · Answer 2 · answered Jun 22 '15 at 10:47

1

USE DISTINCT(SUM()) ,JOIN, GROUP_BY much faster then subquery.

SELECT 
    l.item_id,sum(distinct(l.loss)) 'total_loss',sum(distinct(n.number)) 'total_number'
FROM
   losses_tab l
INNER JOIN numbers_tab n 
ON n.item_id = l.item_id
group by l.item_id,n.item_id   
order by l.item_id

See Online SQL Fiddle

answered Jun 22 '15 at 10:47

Noman

4,088
1
21
36

I can't accept this code..because you are using Distinct keyword.. If there a chance for coming two item_id's have same number ( say 10). so output will be shows 10 instead of showing 20 (10+10) – Nithin Krishnan P Jun 22 '15 at 12:59
yes , but i use `Distinct` just because of your expected result, you can use this query without distinct, you will have expected `SUM` against no of items. – Noman Jun 23 '15 at 05:08

aadarshsg · Answer 3 · 2015-06-22T10:48:22.450

0

SELECT * FROM 
  (SELECT item_id, sum(loss) total_loss FROM losses_tab GROUP BY item_id) losses_tab
INNER JOIN 
  (SELECT item_id, sum(number) total_number FROM numbers_tab GROUP BY item_id) numbers_tab 
ON losses_tab.item_id = numbers_tab.item_id

Edit: Changed the query. Two group by and then inner join by item id.

edited Jun 22 '15 at 10:48

answered Jun 22 '15 at 10:32

aadarshsg

2,069
16
25

i dont want the count.. Sum of numbers with respect item_id in the numbers_tab – Nithin Krishnan P Jun 22 '15 at 10:35

score 0 · Answer 4 · edited May 23 '17 at 11:58

What you are looking for is an inner join with some simple aggregate functions added into the query:

select
    nu.item_id,
    sum(tb.loss) as 'Total Loss',
    sum(nu.loss) as 'Total Number'
from
    numbers nu
        join losses_tab tb
            on nu.item_id=tb.item_id
group by
    nu.item_id

You can get a lot more information on these types of queries in a very lengthy Q&A that I wrote a while back to assist in explaining exactly this sort of question.

How can an SQL query return data from multiple tables

SQL joins two group BY sum

4 Answers4