On Jan 29, 2009, at 10:40 AM, matkinson wrote: > > Hi, > > I'm not replying with an answer, but with a similar problem. I am > using a > TRAC database on top of a SQLite DB and I want to provide a > percentage of > the sum total hours/sum estimated hours. Here's what I'm seeing. > - when only one value (a natural number) is summed and divided, the > answer > is 0 (should be 73/100=73%). > - when multiple lines are summed and divided && the some of the > lines have > fractions (like 7.25, 1.33, etc.), then the answer is correct. > - when multiple lines are summed and divided && the lines are all > natural > numbers, then the answer is 0. > > My query is below. Does this make any sense?
No, it makes no sense. But it does conform to the SQL standard. If you want standards compliance use SUM(). If, on the other hand, you want a sensible answer, use the TOTAL() function instead of SUM(). > > > > SELECT __color__, __group__, __style__, ticket, summary, component , > __milestone__, status, owner, Estimated_work, Total_work, > Percentage_Complete, _ord > > FROM ( > SELECT p.value AS __color__, > t.component AS __group__, > '' as __style__, > t.id AS ticket, summary AS summary, -- ## Break > line here > component, milestone as __milestone__, status, owner, > CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS > NULL > THEN 0 > ELSE CAST( EstimatedHours.value AS DECIMAL ) END as > Estimated_work, > CASE WHEN totalhours.value = '' OR totalhours.value IS NULL > THEN 0 > ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work, > time AS created, -- ## Dates are formatted > changetime AS _changetime, > ROUND((totalhours.value/EstimatedHours.value)*100, 2) as > Percentage_Complete, > reporter AS _reporter > ,0 as _ord > > FROM ticket as t > JOIN enum as p ON p.name=t.priority AND p.type='priority' > > LEFT JOIN ticket_custom as EstimatedHours ON > EstimatedHours.name='estimatedhours' > AND EstimatedHours.Ticket = t.Id > LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' > AND totalhours.Ticket = t.Id > > WHERE 1==1 > > > UNION > > SELECT '1' AS __color__, > t.component AS __group__, > 'background-color:#DFE;' as __style__, > '' as ticket, '' AS summary, > 'Remaining hrs on this charge code' as component, > t.milestone as __milestone__, > CAST( > SUM(CASE WHEN EstimatedHours.value = '' OR > EstimatedHours.value IS > NULL THEN 0 > ELSE CAST( EstimatedHours.value AS DECIMAL ) END) - > SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS > NULL THEN > 0 > ELSE CAST( totalhours.value AS DECIMAL ) END) > AS CHAR(512)) as status, > 'Baseline Est. for this code/Hours Billed already:' as owner, > SUM(CASE WHEN EstimatedHours.value = '' OR > EstimatedHours.value IS > NULL THEN 0 > ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as > Estimated_work, > SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS > NULL THEN > 0 > ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work, > '' as created, > '' AS _changetime, > CAST( > ROUND(100*(SUM(CASE WHEN totalhours.value = '' OR > totalhours.value IS > NULL THEN 0 > ELSE CAST( totalhours.value AS DECIMAL ) END) / > SUM(CASE WHEN EstimatedHours.value = '' OR > EstimatedHours.value IS > NULL THEN 0 > ELSE CAST( EstimatedHours.value AS DECIMAL ) END)), 2) > AS CHAR(512)) as Percentage_Complete, > '' AS _reporter, 1 as _ord > FROM ticket as t > JOIN enum as p ON p.name=t.priority AND p.type='priority' > > LEFT JOIN ticket_custom as EstimatedHours ON > EstimatedHours.name='estimatedhours' > AND EstimatedHours.Ticket = t.Id > > LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' > AND totalhours.Ticket = t.Id > > GROUP BY t.component > ) as tbl > ORDER BY __group__, _ord ASC, ticket > -- > View this message in context: > http://www.nabble.com/Strange-behavior-with-sum-tp4673782p21729338.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
