SQL Select value from other table based on column value as treshold-CodePudding

I have a SQLite query which returns a user name and how much a user spent (done by SELECT SUM() from the different table).

Name	Spent
Adam	700
Mike	400
Steve	100

I have another table which contains discount amount with corresponding treshold:

Treshold	Discount
200	5
400	10
600	15

I need to find what discount each user has (if it does at all). So results would look like this:

Name	Spent	Discount	Total
Adam	700	15	595
Mike	400	10	360
Steve	100	0	100

CodePudding user response：

I am in a hurry. Sorry.

with a as (
select name, sum(spent) spe
from test1
group by name)
select a.name
       , a.spe
       , max(tres)
       , max(disc)
       , spe -spe * (0 || '.' || disc) total
from test2, a 
where tres <= a.spe

DMEO

CodePudding user response：

You need a LEFT join of your query to the 2nd table and aggregation:

SELECT t1.name, t1.Spent,
       COALESCE(MAX(t2.Discount), 0) Discount,
       t1.Spent * (1 - 0.01 * COALESCE(MAX(t2.Discount), 0)) Total 
FROM (SELECT name, SUM(Spent) Spent FROM table1 GROUP BY name) t1
LEFT JOIN table2 t2 ON t2.Treshold <= t1.Spent
GROUP BY t1.name;

See the demo.