websss
asked on
SQL SUM - producing wrong results
Hi
Please look at the queries here
You will see the first results which show the correct results
What I'm trying to do is group the results by days, and give an overall sum for each column
however, i'm getting all -1 results and then werid results for [23-00] column
i'll actually be selecting lots of machines (removing the MachineId=160 bit)
and selecting over a 14 day period, but removed for simplicity
what am i doing wrong
Please look at the queries here
You will see the first results which show the correct results
What I'm trying to do is group the results by days, and give an overall sum for each column
however, i'm getting all -1 results and then werid results for [23-00] column
i'll actually be selecting lots of machines (removing the MachineId=160 bit)
and selecting over a 14 day period, but removed for simplicity
what am i doing wrong
ASKER
I now get
Msg 8117, Level 16, State 1, Line 4
Operand data type bit is invalid for sum operator.
Msg 8117, Level 16, State 1, Line 4
Operand data type bit is invalid for sum operator.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
actually
this worked
this worked
Select BranchId,datename(dw,ReportedDate)ReportedDate,
sum(CONVERT(INT,[06-07])) as [06-07], sum(CONVERT(INT,[07-08])) as [07-08], sum(CONVERT(INT,[08-09])) as [08-09],sum(CONVERT(INT,[09-10])) as [09-10],sum(CONVERT(INT,[10-11])) as [10-11],sum(CONVERT(INT,[11-12])) as [11-12],sum(CONVERT(INT,[12-13])) as [12-13],
sum(CONVERT(INT,[13-14])) as [13-14],sum(CONVERT(INT,[14-15])) as [14-15],sum(CONVERT(INT,[15-16])) as [15-16],sum(CONVERT(INT,[16-17])) as [16-17],sum(CONVERT(INT,[17-18])) as [17-18],sum(CONVERT(INT,[18-19])) as [19-20],sum(CONVERT(INT,[20-21])) as [20-21],
sum(CONVERT(INT,[21-22])) as [21-22],sum(CONVERT(INT,[22-23])) as [22-23],sum(CONVERT(INT,[23-00])) as [23-00],sum(CONVERT(INT,[00-01])) as [00-01],sum(CONVERT(INT,[01-02])) as [01-02],sum(CONVERT(INT,[02-03])) as [02-03],sum(CONVERT(INT,[02-03])) as [02-03],
sum(CONVERT(INT,[03-04])) as [03-04],sum(CONVERT(INT,[04-05])) as [04-05],sum(CONVERT(INT,[05-06])) as [05-06], MachineType
From [UsageLogs]
WHERE BranchId = 51 AND MachineType = 1 and MachineType = 1 and MachineId = 160
group by BranchId, MachineType ,datename(dw,ReportedDate)
This: sum(06-07) as [06-07]
should be: sum([06-07]) as [06-07]