CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Nithin Krishnan P
Nithin Krishnan P

Reputation: 758

SQL joins two group BY sum

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

Upvotes: 1

Views: 70

Answers (4)

aadarshsg
aadarshsg

Reputation: 2089

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.

Upvotes: 0

Noman
Noman

Reputation: 4116

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

Upvotes: 1

Fluffeh
Fluffeh

Reputation: 33522

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

Upvotes: 0

Narendrasingh Sisodia
Narendrasingh Sisodia

Reputation: 21422

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

Upvotes: 1

Related Questions