Help with aggregate functions

I am new to SQL. I am trying to write a query that will join two tables. The join works fine, however I am getting incorrect results. I would like the joined tables to be grouped by ProposalId for the most current ProposalDate listing the ProposalAmount associated with the most current ProposalDate. I have listed the query, the result I get, and the result I would like to get. Any help or direction you could give me would be greatly appreciated.

The SQL script:

SELECT
p.ProposalId, p.ProjectManagerId, p.JobNumber, p.ClientName,
s.[Proposal Date], s.[Proposal Amount], s.[Times Submitted]
FROM
Proposals p join
(SELECT ProposalId 'ProposalId', ProposalAmount 'Proposal Amount', MAX(ProposalDate) 'Proposal Date', COUNT(ProposalId) 'Times Submitted'

FROM
SubmissionHistory
WHERE
ProjectManagerId = 1
GROUP BY
ProposalAmount, ProposalId) s on p.ProposalId = s.ProposalId

This is the result I get:

Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted

1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 1
2 1 01007-001 SAP2007-07-07 10000.00 1
4 1 01008-001 EAS2007-05-30 75000.00 1
4 1 01008-001 EAS2007-05-07 80000.00 1
4 1 01008-001 EAS2007-05-05 90000.00 1

This is the result I would like to get:

Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted

1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 2
4 1 01008-001 EAS2007-05-30 75000.00 3

Quote:

Originally Posted by yoyo35

I am new to SQL. I am trying to write a query that will join two tables. The join works fine, however I am getting incorrect results. I would like the joined tables to be grouped by ProposalId for the most current ProposalDate listing the ProposalAmount associated with the most current ProposalDate. I have listed the query, the result I get, and the result I would like to get. Any help or direction you could give me would be greatly appreciated.

The SQL script:

SELECT
p.ProposalId, p.ProjectManagerId, p.JobNumber, p.ClientName,
s.[Proposal Date], s.[Proposal Amount], s.[Times Submitted]
FROM
Proposals p join
(SELECT ProposalId 'ProposalId', ProposalAmount 'Proposal Amount', MAX(ProposalDate) 'Proposal Date', COUNT(ProposalId) 'Times Submitted'

FROM
SubmissionHistory
WHERE
ProjectManagerId = 1
GROUP BY
ProposalAmount, ProposalId) s on p.ProposalId = s.ProposalId

This is the result I get:

Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted

1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 1
2 1 01007-001 SAP2007-07-07 10000.00 1
4 1 01008-001 EAS2007-05-30 75000.00 1
4 1 01008-001 EAS2007-05-07 80000.00 1
4 1 01008-001 EAS2007-05-05 90000.00 1

This is the result I would like to get:

Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted

1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 2
4 1 01008-001 EAS2007-05-30 75000.00 3

Your core problem is having the ProposalAmount field in the inner query un-aggregated. Either remove it from there or use an aggregation function. I suggest that you reference to the amount in the outer query by proposal ID. (It is however a question how and if you want to have a single amount mixed with aggregated values (dates, times submitted) in the same line. If not, use aggregation inside.)

If you're really new to SQL this is more than decent even so far...|||Thank you for your reply. I appreciate your time. I will give it a shot and see what happens. Once again, thanks!!!!|||Hi i am not sure but i think you are trying to do a union join,, you should try and put in distinct in yout select statements so distinct(ID) for example