Link to home
Create AccountLog in
Avatar of forsters
forstersFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Sql server pivot table summing wrong.

good afternoon, I am having some trouble creating a pivot table.

 
USE [nominals]
GO
set dateformat ymd
select * from
(
 select distinct account_title, (tran_amount + vat_amount) as total,project_code from nominalsview
  where managerid = 1052 and tran_date between '2013-04-01' and '2014-03-31'
 ) x
 pivot
 (
 sum(total)
 for project_code in (CORP1, EMP01, FAC01, FAM01, FIN01, FIR01, MAR01, MAT01, MUN01, 
 PLA01, PRIV1, PRO01, PRO02, REC01, REP01, RES01, ADMIN, CON01, COR01, HR001, IT001,
  LIT01, MIS01, PI001, PRF01, PRL01, PROP1, SSD01, TAX01, TRA01, TRU01)
 ) p;

Open in new window


the table appears to be fine but the summed totals are not correct, its as if some rows aren't being added to the equation. am I missing something really obvious here?
Avatar of Sharath S
Sharath S
Flag of United States of America image

Can you post sample data from your table
Avatar of forsters

ASKER

not really its sensitive info. the columns in the sum function are decimals if that helps. and its obvious that its a case of some rows just not being included in the sum but these rows don't stand out for any reason.
not really its sensitive info.
Nobody is asking you to post the real data.  The keyword here is "sample" data.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Scott your a legend, tidy up the view a bit and removed the distinct from the pivot procedure and its all working well now. Cheers fellah.
Thanks ... it's nice to be a "B"-level legend!
sorry Scott i though i had it on A can it be edited?
LOL, np, you'd have to have it re-opened and then re-close it, not worth the hassle :-)
sorry mate.