Grouping /MSG 8120

Hi

I am new to SQL server Syntax.  I am trying to group group the payment_schedule_id so I get one payment figure for each (please see attached spreadsheet, highlighted in yellow).

Could someone help out please.
Thanks

Greg
select distinct
a.project_payment_id
,h.payment_schedule_id
,a.project_id
,payment_date
,payment_amount
,payment_type
,b.organisation_id
,organisation_name
,organisation_type
,operation_status
,cost_centre_name
,project_name
,servicetypecode
,servicetypename
from project_payment a
inner join project b on a.project_id = b.project_id
inner join organisation c on b.organisation_id = c.organisation_id
inner join project_payment_schedule d on a.project_id = d.project_id
inner join sds.dbo.servicetype on servicetypeid = b.project_type_id
inner join cost_centre e on b.cost_centre_id = e.cost_centre_id
inner join organisation_type f on b.organisation_type_id = f.organisation_type_id
inner join operation_status g on b.operation_status_id = g.operation_status_id
inner join payment_schedule_link h on a.project_payment_id =  h.project_payment_id
inner join payment_type i on a.payment_type_id = i.payment_type_id
where b.organisation_id = 682
and payment_date between '2008-07-01' and '2009-06-30';

Open in new window

Grants-Test.xls
greg_cAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
select
h.payment_schedule_id
,SUM(payment_amount) AS payment_amount
from project_payment a
inner join project b on a.project_id = b.project_id
inner join organisation c on b.organisation_id = c.organisation_id
inner join project_payment_schedule d on a.project_id = d.project_id
inner join sds.dbo.servicetype on servicetypeid = b.project_type_id
inner join cost_centre e on b.cost_centre_id = e.cost_centre_id
inner join organisation_type f on b.organisation_type_id = f.organisation_type_id
inner join operation_status g on b.operation_status_id = g.operation_status_id
inner join payment_schedule_link h on a.project_payment_id =  h.project_payment_id
inner join payment_type i on a.payment_type_id = i.payment_type_id
where b.organisation_id = 682
and payment_date between '2008-07-01' and '2009-06-30';
GROUP BY h.payment_schedule_id
0
Patrick MatthewsCommented:
Or,


SELECT z.payment_schedule_id, SUM(z.payment_amount) AS payment_amount
FROM (
select distinct
a.project_payment_id
,h.payment_schedule_id
,a.project_id
,payment_date
,payment_amount
,payment_type
,b.organisation_id
,organisation_name
,organisation_type
,operation_status
,cost_centre_name
,project_name
,servicetypecode
,servicetypename
from project_payment a
inner join project b on a.project_id = b.project_id
inner join organisation c on b.organisation_id = c.organisation_id
inner join project_payment_schedule d on a.project_id = d.project_id
inner join sds.dbo.servicetype on servicetypeid = b.project_type_id
inner join cost_centre e on b.cost_centre_id = e.cost_centre_id
inner join organisation_type f on b.organisation_type_id = f.organisation_type_id
inner join operation_status g on b.operation_status_id = g.operation_status_id
inner join payment_schedule_link h on a.project_payment_id =  h.project_payment_id
) z
GROUP BY z.payment_schedule_id
0
greg_cAuthor Commented:
I tried the second on and got this:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'payment_type'.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Patrick MatthewsCommented:
Please post the SQL statement exactly as you tried it.  Also, does the SQL statement you put in the original question run correctly?
0
greg_cAuthor Commented:
I cut and paste what was posted.
In answer to your second question, please see the attached file in my first post.

Greg
SELECT z.payment_schedule_id, SUM(z.payment_amount) AS payment_amount
FROM (
select distinct
a.project_payment_id
,h.payment_schedule_id
,a.project_id
,payment_date
,payment_amount
,payment_type
,b.organisation_id
,organisation_name
,organisation_type
,operation_status
,cost_centre_name
,project_name
,servicetypecode
,servicetypename
from project_payment a
inner join project b on a.project_id = b.project_id
inner join organisation c on b.organisation_id = c.organisation_id
inner join project_payment_schedule d on a.project_id = d.project_id
inner join sds.dbo.servicetype on servicetypeid = b.project_type_id
inner join cost_centre e on b.cost_centre_id = e.cost_centre_id
inner join organisation_type f on b.organisation_type_id = f.organisation_type_id
inner join operation_status g on b.operation_status_id = g.operation_status_id
inner join payment_schedule_link h on a.project_payment_id =  h.project_payment_id
) z
GROUP BY z.payment_schedule_id

Open in new window

0
LowfatspreadCommented:
please  state what figure your are trying to achieve


you need to tell us about the structure of your tables..

if you just want a simple sum grouped by  payment_schedule_id

then i find it hard to believe that all these tables are actually required...

explain the joins in business terms

it is unusual to see DISTINCT in a query like this and usually means that a fundamental problem is being overlooked with the underlying data....
(if the system supports multiple payments then it will be perfectly valid for ocasionally 2 payments to be duplicated... the distinct will loose this)

 
 
0
greg_cAuthor Commented:
1) The structure of the tables is in one man's head, and he isn't here
2) I am trying to achieve project_id, payment_scheduleid, etc and one payment amount for the paymnet schedule id.
3) If I remove distinct, I get duplicates.

Greg
0
LowfatspreadCommented:
does this give you the figures you want?

select a.project_id,h.payment_project_id,Sum(payment_amount) as Payment
  From Project_payment as A
 Inner Join payment_schedule_link as H
    on a.project_payment_id=h.project_payment_id
 group by a.project_id,h.payment_project_id
 Order by 1,2

if not

what seems to cause "duplicates " from this

select a.project_id,h.payment_project_id,a.*,h.*
  From Project_payment as A
 Inner Join payment_schedule_link as H
    on a.project_payment_id=h.project_payment_id
 
 Order by 1,2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
greg_cAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.