Have Geek Brain

Using multiple tables, multiple values ​​with Min () SQL

I am using SQLite in an application I am developing.
I am trying to run a rather complex query (tricky for me!) And I got the basic results I want, but I am stuck over the last obstacle.

I currently have this query that does what I need ...

SELECT SUM(activity)
FROM activities
WHERE activity_id IN(SELECT name_id FROM foods GROUP BY name_id HAVING SUM(points) > 20);

I need to add one more part of this query, but here it got a little tricky for me. There are three tables ... dates, products, activities, and I need to find the sum of a result set that contains the minimum number between two values ​​from two different tables, if that statement is true.

Basically..

SELECT SUM(total) FROM (SELECT MIN(value from table1 which is determined by a value in table2, value from table3) AS total
FROM table3
WHERE value from table3 is contained in a result set from table1);

The next query is what I ran into, it will work if there is no syntax (lol!). It doesn't work, but I just wanted to show it to get a better understanding of what I am trying to do.

SELECT SUM(activity_amount) FROM (SELECT min((SELECT SUM(points) - 20 FROM foods WHERE name_id IN(SELECT pk FROM dates WHERE weekly=1) GROUP BY name_id), activity) AS activity_amount
FROM activities
WHERE activity_id IN(SELECT name_id FROM foods GROUP BY name_id HAVING SUM(points) > 20));

The problem lies in the first meaning in MIN()

....

SELECT SUM(points) - 20 FROM food WHERE name_id IN(SELECT pk FROM dates WHERE weekly=1) GROUP BY name_id

This operator gives more than one value, but even if I need those values ​​to compare with others in MIN()

, I only need them one at a time ... not as a whole set.

How can I get something like the above query that I created to work?

EDIT ... some sample tables to help better. Thanks to jellomonkey and hainstech

Table#1(dates)  

CREATE TABLE dates (pk INTEGER PRIMARY KEY, date INTEGER, weekly INTEGER)
pk  date       weekly
1   05062009    1  
2   05072009    1  
3   05082009    2 

Table #2(foods)  

CREATE TABLE foods (pk INTEGER PRIMARY KEY, food VARCHAR(64), points DOUBLE, name_id INTEGER)
pk   food   points   name_id  
 1   food1    12.0     1  
 2   food2    9.0      1  
 3   food3    5.0      1  
 4   food4    15.0     2
 5   food5    14.0     2  
 6   food6    12.0     3  

Table#3(activities)  

CREATE TABLE activities (pk INTEGER PRIMARY KEY, activity DOUBLE, activity_id INTEGER)
pk   activity   activity_id
 1     5.0           1  
 2     4.0           1  
 3     2.0           2  
 4     4.0           3

With this ex and a query from my original post (the one that doesn't work), I would be looking for a result set containing a single value..8.0

MIN (26.0-20, 9.0) = 6.0
MIN (29.0-20, 2.0) = 2.0
6.0 + 2.0 = 8.0

Hope this helps!

0


source to share


2 answers


After much scratching and guessing, I suspect you want:

SELECT SUM(MIN(fp-20, ap)) FROM
  (SELECT dates.pk AS fd, SUM(points) AS fp
  FROM dates
  JOIN foods ON name_id = fd
  GROUP BY fd
  HAVING fp >= 20)
    JOIN
  (SELECT dates.pk AS ad, SUM(activity) AS ap
  FROM dates
  JOIN activities ON activity_id = ad
  GROUP BY ad)
    ON fd = ad


The column names seem to have a null join to their value, but hey at least that gives 8.0 and the subqueries give the other numbers you mention! -)

+2


source


Agree with jellomonkey - I can't figure out exactly what you want without showing more information. Simple table definitions, a few lines of sample data, and the expected output will help.

Here's a simple example of getting the minimum value from a table based on a range that is bounded in the other two tables:

create table t1(t1id int primary key, value int)
create table t2(t1id int, t2id int primary key, value int)
create table t3(t2id int, t3id int primary key, value int)

select
    MIN(t1.value)
from table1 as t1 
    join table2 as t2 on t2.t1id = t1.t1id
    join table3 as t3 on t3.t2id = t2.t2id
where t1.value between t2.value and t3.value

This uses the t2.value and t3.value as the range bounds for MIN () in lookups.



EDIT - thanks for posting the example tables, which helps a ton. You can use some collapsed CASE statements to compare two values, but I would like to cast them into one rowset and do the usual MIN aggregation. This will be more readable and also allow you to more easily add an additional set of information to include in your MIN inputs, if needed. So here's a quick hit on what I'll be doing:

select
    SUM(activity)
from (
    select
        min(activity) as activity
        ,activity_id
    from (
        select
            SUM(activity) as activity, activity_id
        from activities 
        group by activity_id
        union all
        select
            SUM(points)-20
            ,name_id
        from foods 
        group by name_id
        having SUM(points) > 20
    ) as activitySum
    where activity_id in (select pk from dates where weekly=1)
    group by activity_id
) as activityLesserOf

Edit # 2 - Added to the following request to clarify needs

+1


source






More articles:


All Articles