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
3 1301
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...
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ariel Jakobovits |
last post by:
I have a table with 2 primary keys, one is a foreign key, the other is
produced by a sequence.
I want to SELECT query for one record that has a list of the
sequence-produced values for all...
|
by: Job Lot |
last post by:
Is it possible to use Aggregate functions with GROUP BY Clauses on DataTable. I have a DataTable with following values:
Date Amount Int Balance
1/1/2004 5000.00 50.00 5050.00...
|
by: jc |
last post by:
Hi.
Just as we have AVG(COLUMN_NAME) and MAX(COLUMN_NAME) how can I write
my own variation of a such a function. If I can appreciate how to do
this,
then I hopefully I can write a...
|
by: neb |
last post by:
Dear member of the forum,
Ms access has built-in aggregate function like:
-Sum, Max, First, Avg, ...
Is it possible to build user-defined aggregate?
(if you have any clue, do not hesitate to...
|
by: David Garamond |
last post by:
What do people think of adding some more aggregate functions. These are
the ones that MySQL has and PG doesn't:
- STD/STDDEV
- VARIANCE
- BIT_OR
- BIT_AND
- GROUP_CONCAT (for strings, added...
|
by: S P Arif Sahari Wibowo |
last post by:
Hi!
I would like to make an editable continous form, where most
fields will be from table A and editable, except 1-3 fields are
a glimpse into table B and uneditable. Table A relate to table B...
|
by: jefftyzzer |
last post by:
The current issue of "Oracle Magazine" has an article on creating
custom aggregate functions, which naturally got me thinking about how
to do this in DB2.
I found some articles on creating...
|
by: Dean |
last post by:
Has anyone toiled with creating/using alternate domain aggregate
functions? I have been messing with that a little. The one
recordsource I have been working indicates I get 20 to 40% savings in...
|
by: Ian825 |
last post by:
I need help writing a function for a program that is based upon the various operations of a matrix and I keep getting a "non-aggregate type" error. My guess is that I need to dereference my...
|
by: Chuck Cheeze |
last post by:
This might be in the wrong group, but...
Here is an example of my data:
entry_id cat_id
1 20
2 25
3 30
4 25
5 35
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |