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
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
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.
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.
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