CODEWITHSUNDEEP
mysqlsqlgroup-byinner-joinwhere-clause
Led
Led

Reputation: 517

JOIN, GROUP BY, SUM Issue Mysql

Assuming I have this table

tableA
ID       value 
1          5
1          5
3          10
2          4 
2          2
1          2

tableB
ID        Name       
1         apple      
2         carrot      
3         banana

If the expected max value of apple is 10, carrot is 5, and banana is 15 the output table would be

table output
ID     Name       value
1      apple      12
2      carrot     6

what SQL statement I need to solve this?

what I have done so far:

SELECT a,ID, b.name , sum(a.valueSUM) AS value FROM tableA a
INNER JOIN tableB b 
ON a.id = b.id 
GROUP BY id

what options i need on the WHERE clause to pull this off?

Upvotes: 2

Views: 190

Answers (4)

bvr
bvr

Reputation: 4826

This works in SQL Server

--Existing tables
create table #tableA (ID int, value int)
create table #tableB (ID int, Name varchar(30))


insert into #tableA
select 1 , 5 union all  
select 1 , 5 union all  
select 3 , 10 union all  
select 2 , 4 union all  
select 2 , 2 union all  
select 1 , 2

insert into #tableB    
select 1 , 'apple' union all  
select 2 , 'carrot' union all 
select 3 , 'banana'   

--Create new temporary table @tableC

create table #tableC (ID int, MAXvalue int)


insert into #tableC    
select 1 , 10 union all  
select 2 , 5  union all  
select 3 , 15 


select c.ID,b.Name, a.value from #tableC c
inner join #tableB b on b.ID = c.ID
inner join (
    select ID,SUM(value) as value from #tableA
    group by ID
) a on a.ID = c.ID
where a.value >= c.MAXvalue

drop table #tableA
drop table #tableB
drop table #tableC

Upvotes: 0

beejm
beejm

Reputation: 2481

The inner subquery groups them normally and then the main query is what deals with limiting the results.

SELECT * FROM 
     (select 
      b.id,
      b.name as name, 
      SUM(a.value) as the_sum
      from tableA a inner join tableB b 
      on a.Id = b.id 
      group by b.name, b.id
     ) y
where (name = 'apple' and the_sum >= 10) OR
  (name = 'banana' and the_sum >= 15) OR
  (name = 'carrot' and the_sum >= 5)

It seems your sample data has changed, please try this. I thought the ID doesnt have to follow tableA/tableB's id and the id is auto-generated as per the results.

Would be nice if you have another table that sets the threshold per name

Upvotes: 2

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5940

Assuming threshold can be specified in tableB (makes sense):

SELECT a.ID, b.name, sum(a.value) AS value
FROM tableA a
INNER JOIN tableB b 
ON a.id = b.id 
GROUP BY a.ID, b.name, b.Threshold
HAVING sum(a.value) > b.Threshold;

Demo: http://rextester.com/ICOQF10295

Upvotes: 2

Tharsan Sivakumar
Tharsan Sivakumar

Reputation: 6531

SELECT TableB.id, TableB.Name, MAX(TableA.value) AS Value
FROM TableA INNER JOIN TableB ON
TableA.id = TableB.id
GROUP BY TableB.id, TableB.Name

Instead of SUM, use MAX aggregate function

Upvotes: 0

Related Questions