CTE Incorrect Numbers on Total

Question

Post reply

CTE Incorrect Numbers on Total

Welsh Corgi

SSC Guru

Points: 116520
More actions
November 15, 2011 at 12:40 pm

#246239

Marketing GrpDFCntHO2CntHO3CntHO4CntHO6ContTotal
Joe Smo's001012
Advanced 001012
XYZAG 100012
Ya Ya Ya000011
Ha Ha Ha000011
Al Ganza001011
To Nowhere001000
No Cost001000
Life West100000
Bonzo's 000011
SELECT TOP 10 [Group],SUM(DFCount) AS DFCount,
SUM(HO2Count) AS HO2Count,SUM(HO3Count) AS HO3Count,
SUM(HO4Count) AS HO4Count,SUM(HO6Count) AS HO6Count--,
--SUM(DFCount) + SUM(HO2Count) + SUM(HO3Count) +
--SUM(HO4Count + SUM(HO6Count)
FROM CTE
GROUP BY [Group]
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

What is the issue? From the sample you posted it looks like your totals are correct.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Sean Lange (11/15/2011)
What is the issue? From the sample you posted it looks like your totals are correct.

Sean,

Sorry I fuged the numbers, I have been here over 9 hours no break, the totals are wrong.

I put the correct number in the output:w00t:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Well given that you didn't post any ddl, or sample data it is impossible to tell what might be wrong.

The query you posted looks on the surface like it would be correct.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Sean Lange (11/15/2011)
Well given that you didn't post any ddl, or sample data it is impossible to tell what might be wrong.
The query you posted looks on the surface like it would be correct.

Sean,

I'm sorry, it is correct.

I should post DDL and sample Date but there are a lot of tables involved and I need to take time to create dummy DDL Structures and Sample Data.

Thank you for responding.:doze:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

No problem. Sounds like you need to find a way to get some time off. 😉

Let me know if still need some help with this and we can take a stab at it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Sean,

That was a dumb mistake but at least it was not something difficult.:laugh:

Thanks! I had two CTE's based on different DB's but almost identical.

I'm done with that but I just started to create a Report in Reporting Services and I'm trying to determine how to Set the Data Source as an SP.

Thanks again.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/