[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

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
0
greg_c
Asked:
greg_c
  • 4
  • 3
  • 2
1 Solution
 
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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
 
greg_cAuthor Commented:
Thank you
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now