No longer grouping correctly

For some reason, rmsacctnum is not being grouped and the 2 results I get should be added together at the end for , same for Sum rmstranamt and Sum rmstranamt 10 like in my 2nd query below. The first query is where the problem lies:

SELECT rm.rmsacctnum,
SUM(rf.rmstranamt) AS [Sum rmstranamt],
SUM(rf10.rmstranamt10) AS [Sum rmstranamt 10],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
WHEN SUM(rf.rmstranamt) = 0 AND SUM(rf10.rmstranamt10) = 0 Then
0.00
WHEN SUM(rf.rmstranamt) = 0 AND SUM(rf10.rmstranamt10) <> 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
WHEN SUM(rf.rmstranamt) <> 0 AND SUM(rf10.rmstranamt10) = 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance

FROM RMASTER rm

INNER JOIN
(
SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS
) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
WHERE rf.rmstrancde IN ('10', '16','18','19','20','21','22','29','30','31','36','37','38','3A','3B','3C','3D','3E','3F','3M','3N','3O','3P','3Q','3R','3T',
'3U','3X','3Z','40','41','42','43','44','45','46','47','48','49','4A','4B','4D','4E','4H','4J','4X','4Z','50','51','52','53',
'55','56','57','58','5A','5B','5C','5P','5Q','5R','5X','5Z')
AND rm.rmsacctnum = '4313030999894992'
GROUP BY rm.rmsacctnum, cb.CurrentBalance, rf.rmstranamt
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00

OUTPUT:

-
4313030999894992 85.00 3265.12 3180.12 3528.43 <-- rmsacctnum is not grouped anymore!
4313030999894992 178.31 3265.12 3086.81 3528.43

Before I added the CASE Statements I had this and it was grouping and summing fine:

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

INNER JOIN
(
SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS
) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM

WHERE rm.rmsacctnum = '4313030999894992'
GROUP BY rm.rmsacctnum, cb.CurrentBalance
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00

OUTPUT:

-

4313030999894992 263.31 6530.24 6266.93 3528.43

SOLVED IT

I had some missing SUM in my Case. Then took out rf.rmstranamt in my GROUP BY

No HTML page breaks and slow expanding rows

I have a report with a single table, single grouping level, single data set and no sub-reports. It has 3 rows for a grouping header and 3 rows per dataset row of detail. The detail rows are initially hidden and can be expanded by clicking on the header +. Its a fairly standard master-detail report.

Regardless of data size, I get NO page breaks in HTML. I have the Interactive size set to 8.5x11, KeepTogether is set to False, and PageBreakAtEnd is set to False. I would like it to break based on the visible grouping rows.

As it is now, everytime you expand any section, it takes forever to reload for a larger recordset.

I know that "HTML renderer and Preview (which are soft page break renderers) will ignore page breaks of conditionally hidden items and their children.", but how do I get this report to page break? I've seen a lot of posts on this, but none that seem to have an answer.

Anyone? Can I programmatically add the soft page breaks? The report is useless as it is now.|||

Thanks to others, I found a solution. Use the query to calculate the count of headers using the dense_rank() function:

select ...

dense_rank() over ( order by cl.last_name, cl.first_name, cl.client_id ) AS ClientRank

Then add a top level grouping on the table set to "Page Break at End" with the formula:

=Ceiling(Fields!ClientRank.Value/15)

replace 15 with how many group headers you want per page.

-Dave

sql

No grouping?

Hi to all!

To count(something), it is usually required to use a group by function, however, that is not applicable in this case: I would like to select records that have similar values for 'col2' and 'col3', given a certain 'col1', and how many records each 'col1' returns. The following example hopefully makes it clear.

The table:
Col1 Col2 Col3

s1 A B
s1 C D
s2 A B
s2 C D

The result should be:
col1 col2 col3 Expr

s2 A B 2
s2 C D 2

I had figured out the not-working-query:

SELECT *, count(col1)
FROM table as t1 INNER JOIN table as t2 ON
t1.col2 = t2.col2 AND t1.col3 = t3.col3
WHERE col1 = 's1'

Could anyone provide me a hand? Many thanks in advance.

Kind regards,

Nele

SELECT Col1,Col2,Col3,Count(Col1)

FROM TEST

GROUP BY Col1,Col2,Col3

|||

No, that returns for 'expr' value 1.

|||

Are you trying to find those records where value of COL2 and COl3 are similar, which means there is more than one record having same value for col2 and col3

Col1 Col2 Col3

s1 A B
s1 C D
s2 A B
s2 C D

s3 C A

IF I get it correctly, here the last record should not be displayed. Is that what you intend?

|||

In SQL 2000, you can do:

select *, (select count(*) from table as t2 where t2.col1 = 's1') as cnt

from table as t1

where t1.col1 = 's1'

In SQL 2005, you can do:

select *, count(*) over(order by t1.col1) as cnt

from table as t1

where t1.col1 = 's1'

Having said this, there is really no need to complicate the query by doing this. You will only get slow performance. The number of rows that qualifies the query can be obtained by looking at @.@.ROWCOUNT after the SELECT statement or determined in the client side easily. So it seems unnecessary to do this .

|||Yes! That is what I intend.|||

I tried your code, but for sql 2005 it returned an error "incorrect syntax near the keyword 'order' "?

@.@.rowcount returns the total number, I rather would like to know how many records of a certain value for 'col1' are returned (to select the top(100) in the end). E.g., for 's2' that is 2.

|||

How's about:

Select Col2, Col3, Count(distinct col1 )

From ...

Group by Col2, Col3

Having Count(distinct col1) > 1

And if you want Col1 to display, you can add use

Select Max(Col1), Col2, Col3, Count(distinct col1)

|||

Sorry that should be partition by not order by. We don't support ORDER BY clause for aggregate function yet in OVER clause. We support only partition clause.

|||

No, the problem is that I want to know how many times a certain value for 'col1' occurs, not taking into account the values of col2 and col3 for that calculation.

Besides, all have an nvarchar as type.

|||

Yes, that is what I wanted to achieve

Thanks for the reactions!

sql