开发者

Help with SQL totals and sums

I have this SQL. Im not sure it is what i need, but it does select all the records i need. I just need to condense them now and total the counts and amounts for unique dates.

select m.account_tag, m.cmcl_forecast_cleared, m.check_amount,  
a.acct_id, a.acct_no, a.bank_id,  
b.bank_id, b.name  
from ap_开发者_运维问答master m   
join accounts a on a.acct_id=m.account_tag  
join banks b on b.bank_id=a.bank_id  
where m.cmcl_bank_cleared is null 
order by m.account_tag, m.cmcl_forecast_cleared  

I only want to display four columns Account, Date, Count, and sum

The account would be a.acct_no.

The date would all unique m.cmcl_forecast_cleared date for that account

The count would be total no of checks (records) for those unique dates (cmcl_forecast_cleared)

The sum would be the total check_amount for thos unique dates (cmcl_forecast_cleared)

I hope this is understandable. Im not sure if i need any grouping or not


select
  a.acct_no, m.cmcl_forecast_cleared, b.name,
  count(*) as TotalChecks,
  Sum(m.check_amount) as TotalAmount
from ap_master m   
join accounts a on a.acct_id=m.account_tag  
join banks b on b.bank_id=a.bank_id  
where m.cmcl_bank_cleared is null 
group by a.acct_no, m.cmcl_forecast_cleared, b.name
order by a.acct_no, m.cmcl_forecast_cleared, b.name

You group by as many columns as you need.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜