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?
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
