Group by to get distinct values

[PETEHO]

I have the following code but I can't get it to give me individual sums for debits and credits for each journal.
I just want a list of journals names with their debit and credit values as one summed value which should match to each other in theory but our journals can be 20000 lines long.

Code and results below

Please can you assist

Thanks Pete

-----------------------------------------

drop table #txn_master
select top 0
' ' company_code,
journal_number journal_id,
post_date posted_date,
trans_period period,
origin doc_type,
case when SUM(journal_amount) > 0 then SUM(journal_amount) else 0 end debit_posting,
case when SUM(journal_amount) < 0 then SUM(journal_amount) else 0 end Credit_posting


into #txn_master
from PR001.scheme.nltranm
group by journal_amount,journal_number,post_date,trans_period,origin
exec msdb..sp_MSforeachdb 'if ''?'' in (''CS354'',''CRL01'')
begin
insert into #txn_master
select
''?'' company_code,
journal_number journal_id,
post_date posted_date,
trans_period,
origin doc_type,
case when SUM(journal_amount) > 0 then SUM(journal_amount) else 0 end debit_posting,
case when journal_amount < 0 then journal_amount else 0 end credit_posting

from ?.scheme.nltranm
where nlyear = ''14'' and trans_period = ''12''
group by journal_amount,journal_number,post_date,trans_period,origin
order by journal_number
end','?'

select * from #txn_master
-----------------------------------
results below small extract
-----------------------------------

company_code journal_id posted_date period doc_type debit_posting Credit_posting
CRL01 C3PRL23C14 2014-12-24 00:00:00.000 12 NJ 7427.28 0
CRL01 C3PRL23C14 2014-12-24 00:00:00.000 12 NJ 0 -7075.52
CRL01 C3PRL23C14 2014-12-24 00:00:00.000 12 NJ 0 -491.53
CRL01 C3PRL23C14 2014-12-24 00:00:00.000 12 NJ 0 -381.68
CRL01 C3PRL23C14 2014-12-24 00:00:00.000 12 NJ 0 -264.52
CRL01 C3PRL23C14 2014-12-24 00:00:00.000 12 NJ 0 -147.11
CRL01 C3PRL23C14 2014-12-24 00:00:00.000 12 NJ 0 -114.03

 

[gmmastros]

try removing journal_amout from your group by clauses:

group by [s]journal_amount,[/s]journal_number,post_date,trans_period,origin

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

I'd even start with [tt]GROUP BY journal number[/tt] only, no further columns to group by. The more columns you specify in GROUP BY, the fewer records belong to each group - up to the point where you have each record in its own group.

It's very obvious you can't sum all distinct amounts values and let each amount value (in conjunction with further group defining columns) be a separate group, so its at least a column to not group by.
Another hint on what to include and what not: If you want the origin in the result #txn_master table, you have to group by it, but you will get a sum for each origin of each journal_number, not a total sum of all journal_number related amounts.

Also I think you rather want to [tt]SUM(case when journal_amount > 0 then journal_amount else 0 end) as debit_posting[/tt] and [tt]SUM(case when journal_amount < 0 then -journal_amount else 0 end) as Credit_posting[/tt], otherwise you get one of the result columns 0 and the total of all debit and credit transactions as either debit or credit just depending on the sign of the total.
The way I propose you will have two figures and the overall total will be debit_posting-credit_posting. I'd say that is a more precise and desirable result, but that is not necessarily your goal, just an observation and guess of what you might rather want.

Bye, Olaf.