Sum excluding Duplicates in - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Sum excluding Duplicates in

Author

Topic

Latha27
Starting Member

20 Posts

Posted - 2011-11-16 : 11:34:10

Hi

I'm a novice to SSRS 2008. I need to add a sum excluding the duplicates records(in red). I do the code 0 for other ID.

What i need is the grand total in Blue and not the one in purple..

ID Code Type Sales Sales1
12333 0 1 $4,010.00 $-
22222 3 1 $4,582.04 $2,928.43
45666 0 2 $5,271.00 $-
3 1 $5,271.00 $3,075.87
11111 2 1 $75,790.10 $16,757.40
65755 2 1 $19,500.00 $7,215.00
29544 0 2 $6,276.00 $-
3 1 $6,276.00 $4,080.87
29554 0 2 $16,405.00 $-
2 1 $16,405.00 $5,741.75
Wrong Total $ 159,786.14 $ 41,002.32 Correct Total $131,834.14 $41,002.32

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 11:40:36

can you explain what according to you are duplicates?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Latha27
Starting Member

20 Posts

Posted - 2011-11-16 : 11:56:13

This is what i have now.

ID Code Type Sales Sales1
45666 0 2 $5,271.00 $-
3 1 $5,271.00 $3,075.87
29554 0 2 $16,405.00 $-
2 1 $16,405.00 $5,741.75

I need this

ID Code Type Sales Sales1
45666 3 1 $5,271.00 $3,075.87
29554 2 1 $16,405.00 $5,741.75

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 11:59:06

Ok...so alignment is wrong.
Can you show the backend query for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Latha27
Starting Member

20 Posts

Posted - 2011-11-16 : 12:09:57

SELECT ID, Code,Type,Sales, Sales1
FROM [Sales]

WHERE Code IN (0,2,3)

And i have these expressions in report builder 2.0
ID Code Type Sales Sales1
ID Code Type Sales Sales1
[CountDistinct(ID)] [Sum(Sales)] [Sum(Sales1)]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 12:15:12

make the query like


SELECT ROW_NUMBER() OVER (PARTITION BY ID,Sales ORDER BY Code DESC) AS Rn,ID, Code,Type,Sales, Sales1 
FROM [Sales]
WHERE Code IN (0,2,3)

then use expression like below for total in report

=SUM(IIF(Fields!Rn.value=1,Fields!Sales.Value,0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Latha27
Starting Member

20 Posts

Posted - 2011-11-16 : 13:28:53

This expression is giving me #Error

then use expression like below for total in report

=SUM(IIF(Fields!Rn.value=1,Fields!Sales.Value,0))

Latha27
Starting Member

20 Posts

Posted - 2011-11-16 : 17:46:06

Thanks alot . I got it working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 00:59:39

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Latha27
Starting Member

20 Posts

Posted - 2011-11-17 : 10:47:38

How do you sort it by month and year in the same query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 11:17:22

quote:
Originally posted by Latha27

How do you sort it by month and year in the same query?

do you mean this?


ORDER BY YEAR(Datefield),MONTH(datefield)

you can also do it in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Latha27
Starting Member

20 Posts

Posted - 2011-11-17 : 11:54:25

I have My Header Title as "Reporting Period 10-2011" using the expression below

= "Reporting Period" & " " & Month (Parameters!parm_Start_Completed_Dte.Value) & "-" & Year(Parameters!parm_Start_Completed_Dte.Value)

But i need "Reporting Period October 2011". How do i do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 12:05:51

= "Reporting Period" & " " & MonthName(Month(Parameters!parm_Start_Completed_Dte.Value)) & "-" & Year(Parameters!parm_Start_Completed_Dte.Value)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Latha27
Starting Member

20 Posts

Posted - 2011-11-17 : 12:12:58

Thanks a lot.I'm learning more and more day by day. Have a good day.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 12:47:07

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Latha27
Starting Member

20 Posts

Posted - 2011-11-17 : 16:58:53

This is what i have

Act Sales Sales1
88.28 $4853.87 $2418.60
25.78 $3581.58 $90.00
88.27 $8513.00 $915.00

I want if any Act 25.78 with sales 1 value, i want it to be 0:

Act Sales Sales1
88.28 $4853.87 $2418.60
25.78 $3581.58 $0.00
88.27 $8513.00 $915.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 03:17:26

do you mean this?

=SUM(IIF(Val(Fields!Act.value)=25.78,0,Fields!Sales1.value))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Latha27
Starting Member

20 Posts

Posted - 2011-11-18 : 10:41:05

TQ.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 11:28:56

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Latha27
Starting Member

20 Posts

Posted - 2011-11-21 : 13:32:10

I have this:

ID Total Sales Total Sales W Sav Sales Sales Sav
11111 1 1 $4000 $1200
11333 1 1 $6500 $100
11111 1 1 $9000 $0But i need this:
ID Total Sales Total Sales W Sav Sales Sales Sav
11111 1 1 $4000 $1200
11333 1 1 $6500 $100
11111 1 0 $9000 $0

How do i write an expression based on that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 01:23:44

On what basis you made it 0? is it based on ID value (0 for all duplicates)?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources