Sybase - Group by clause not giving expected Result

May 24, 2017 at 02:38:15
Specs: Windows 7
Hi Team,

I need your help on below code, Im having one table #MyBank which has 20 records, I want to sum up TotalAmount and create combined StampId if DebitDate and InvoiceType are all same. Please refer the below code snippet. In the 20 records there are 4 combination which have DebitDate and InvoiceType are all same. When i run the query the expected result should be four records. But the Problem is the column InvoiceGroupID is not getting grouped properly and its giving more records like 13 records.

Can someone please help me on the below query so that i could get exact expected results (4 sets)

select Distinct
InvoiceGroupID,
MsgID,
IsFurikomiPrinted,
'Combined' StampId,
InvoiceType,
BeneficiaryBank,
BeneficiaryBranch,
BeneficiaryAccType,
BeneficiaryAccNo,
BeneficiaryName,
BeneficiaryNamexyz,
Sum(TotalAmount)TotalAmount,
RemitterName,
RemitterNamexyz,
DebitDate,
Address,'' "Phone"
from #MyBank
group by DebitDate having count(InvoiceType+DebitDate) >1


See More: Sybase - Group by clause not giving expected Result

Reply ↓  Report •

#1
May 24, 2017 at 05:12:20
Can anyone who knows Sybase please help me on this ?

Reply ↓  Report •

#2
June 10, 2017 at 22:36:26
Would it be possible to share those 20 set of data so that I will look and try the query?

message edited by rkptcs


Reply ↓  Report •

#3
June 12, 2017 at 00:55:32
InvoiceGroupID AccountNumber DebitDate TotalAmount InvoiceType InvoiceCategory MsgID RemitterName RemitterNameJxyz BeneficiaryBank BeneficiaryBranch BeneficiaryAccType BeneficiaryAccNo BeneficiaryName BeneficiaryNamexyz
Address IsFurikomiPrinted StampId RowNum
---------------- ------------- ---------- --------------- ---------------------------------------------------------------------------------------------------- --------------- --------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------ ---------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------- --------------- -----------
140514_000000014 0157850000 12-12-2014 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
157850 N 191 1
140514_000000017 0157850000 12-12-2018 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
157850 N 111 2
140701_000000005 0159019000 30-10-2015 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
159019 N 100000 5
140703_000000010 0159019000 30-10-2015 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
159019 N 100000 6
140703_000000016 1234567890 08-08-2018 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
NULL N 1 7
140707_000000002 0159019000 30-10-2015 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
159019 N 100000 8
140707_000000004 0159019000 30-10-2015 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
159019 N 100000 9
140703_000000016 1234567890 08-08-2018 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
NULL N 1 10
140710_000000001 0100109000 31-12-2014 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
100109 N 30701 11
140715_000000003 1234567890 08-08-2018 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
NULL N 2 12
140715_000000004 1234567890 08-08-2018 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
NULL N 2 14
140715_000000007 0100109000 01-01-2015 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
100109 N 71501 15
140715_000000008 1234567890 08-08-2018 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
NULL N 3 16
140715_000000007 0100109000 01-01-2015 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
100109 N 71501 17
140717_000000002 1234567890 08-08-2018 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
NULL N 8 18
140717_000000005 1234567890 08-08-2018 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
NULL N 3 19
140725_000000003 1234567890 08-08-2018 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
NULL N 19 20
140728_000000001 1234567890 08-08-2018 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
NULL N 20 21
141104_000000001 2014073101 07-08-2015 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
NULL N 110401 23
141104_000000001 2014073101 07-08-2015 18533 abcd 1 6789 \A\1\E!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! A?A asd asdas Saving 1234567890 £C£I£I oaI3Eotest
NULL N 110401 24

(20 rows affected)


Reply ↓  Report •

Related Solutions

#4
August 31, 2017 at 08:36:57
there's nothing good ...

Reply ↓  Report •

Ask Question