Django : Writing a query with condition referencing aggregation within same table

Question

I have a table similar to this:

id  name   added     count  flag
---------------------------------
01  abcd1  09.02.13    4    false
02  abcd1  10.02.13    1    true  
03  abcd1  11.02.13    3    false
04  abcd1  12.02.13    4    false

I need to find sum of count for a given name with the following conditions:

For a given name
Flag should be false
Entry should be added after the last flag=true entry for that name

In this case I want sum of count for entries 03 and 04.

How should I do this in Django?

EDIT:
I am looking for creating a query like this:

SELECT id from sample
WHERE 
  name = 'abcd1' AND
  added > (SELECT MAX(added) FROM sample WHERE name = 'abcd1' AND flag = TRUE)

EDIT: Also if there was no entry with flag=true then I need all the entries for that name.

score 1 · Answer 1 · answered Mar 31 '13 at 00:17

1

How about

select name, sum(count) as count
from T as t
where flag = 'false'
and exists (
    select 1
from T
where name = t.name
and flag = 'true'
group by name, flag
having max(added) < t.added
)
group by name

answered Mar 31 '13 at 00:17

James K. Lowden

6,629
1
13
28

Performance is a function of implementation. Good query optimizers "flatten" the query, treat it more or less like a join. Expressing the query as simply as possible gives both man and machine the best opportunity of understanding it. – James K. Lowden Apr 17 '13 at 06:08
When exist condition is checked, does the sub-query within 'exists' run for every row? Or the query optimizer only executes it once? I just need to understand how it works. Thanks! – maulik13 Apr 17 '13 at 09:59
Many people think so, but the answer is No. There's no rule about *how* the DBMS executes the query. There are ways of thinking about it, and of course particular implementations have specific behavior. To answer your question for your DBMS, the best answer is RTFM. :-) – James K. Lowden Apr 18 '13 at 05:19

Bohemian · Accepted Answer · 2013-04-04T20:16:41.647

1

It's fairly straightforward if you use a little subquery:

select
    name,
    sum(count)
from sample s
where flag = false
and (added > (select max(added)
    from sample
    where name = 'abcd1'
    and flag = true)
    OR
    not exists (
        select *
        from sample
        where name = 'abcd1'
        and flag = true)
    )
and name = 'abcd1'

Note the use of the OR not exists clause to handle the case when there are no entries with flag=true.

edited Apr 04 '13 at 20:16

answered Mar 31 '13 at 00:36

Bohemian

365,064
84
522
658

Do you know how to translate this into Django query? – maulik13 Mar 31 '13 at 09:09
Sorry, I am unfamiliar with django. Perhaps try a django forum. – Bohemian Mar 31 '13 at 11:08
No problem. Thanks for confirming the SQL query. – maulik13 Mar 31 '13 at 11:11
How do you handle the situation if there is no entry with flag=true? I still need the sum but now I need for all flag=false entries for a given name. SQL is fine if you could help me. Thanks. – maulik13 Apr 04 '13 at 17:03
For going "above and beyond", would you Ming up-voting both my answer *and* the accepted answer to [this question](http://stackoverflow.com/questions/7054972). Thx :) – Bohemian Apr 04 '13 at 20:20
Sure :) I realized my question was not complete when I did some testing, so I have edited the question as well. – maulik13 Apr 05 '13 at 07:15

Django : Writing a query with condition referencing aggregation within same table

2 Answers2