469,934 Members | 2,173 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

Help with aggregate functions

2
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 SAP 2007-07-09 8000.00 1
2 1 01007-001 SAP 2007-07-07 10000.00 1
4 1 01008-001 EAS 2007-05-30 75000.00 1
4 1 01008-001 EAS 2007-05-07 80000.00 1
4 1 01008-001 EAS 2007-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 SAP 2007-07-09 8000.00 2
4 1 01008-001 EAS 2007-05-30 75000.00 3
Aug 1 '07 #1
3 1135
azimmer
200 Expert 100+
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 SAP 2007-07-09 8000.00 1
2 1 01007-001 SAP 2007-07-07 10000.00 1
4 1 01008-001 EAS 2007-05-30 75000.00 1
4 1 01008-001 EAS 2007-05-07 80000.00 1
4 1 01008-001 EAS 2007-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 SAP 2007-07-09 8000.00 2
4 1 01008-001 EAS 2007-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...
Aug 2 '07 #2
yoyo35
2
Thank you for your reply. I appreciate your time. I will give it a shot and see what happens. Once again, thanks!!!!
Aug 2 '07 #3
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
Aug 3 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Ariel Jakobovits | last post: by
5 posts views Thread by David Garamond | last post: by
8 posts views Thread by jefftyzzer | last post: by
118 posts views Thread by Chuck Cheeze | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.