subquery error

[slimjen]

All, trying to create this subquery but I get an error: the select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. This is my code:

SELECT  tblCust.Custid, tblgross.[grossreceipts], Max(SumOfgrossreceipts) AS [MaxOfSumOfgross receipts]
(SELECT tblCust.Custid, tblgross.grossreceipts, tblCust.Custid, Sum(tblgross.grossreceipts)AS [SumOfgrossreceipts]
FROM tblgross LEFT JOIN tblCust ON tblgross.custid = tblCust.custid
GROUP BY tblCust.Custid, tblgross.grossreceipts, tblCust.costid)
FROM tblCust LEFT JOIN tblgross ON tblgross.custid= tblCust.custid;

Can someone help please? Thanks

[pbaldy]

I'm not clear on your goal, but as you have it there would need to be a comma before the subquery. That said I don't think it will work. You realize you can get the Sum and Max in a single query?

[slimjen]

I was trying to keep from writing multiple queries. And I didn't think I could get the sum and max in one query. That was my ultimate goal was to write a single query getting the sum then max for the gross receipts. But could not get it to work which is why the subquery. Help please??

[pbaldy]

Try this, though there are a couple of questionable things in there.


SELECT tblCust.Custid, tblgross.[grossreceipts], Max(SumOfgrossreceipts) AS [MaxOfSumOfgross receipts],
Sum(tblgross.grossreceipts) AS [SumOfgrossreceipts]
FROM tblgross LEFT JOIN tblCust ON tblgross.custid = tblCust.custid
GROUP BY tblCust.Custid, tblgross.grossreceipts

[slimjen]

I get error: subqueries cannot be used in the expression (Max(SumOfgrossreceipts))
What are the questionable things please?

[pbaldy]

Is that the name of a field? Concerns include both grouping on and summing the same field and grouping on a field tblCust because of the LEFT JOIN.

[slimjen]

yes; I am trying to get the sum and max of gross receipts. I'm guessing this is not possible in one query?

[pbaldy]

I already said it was. I think you made up some field names. See if this simpler version works:

SELECT Custid, Max(grossreceipts) AS [MaxOfgross receipts],
Sum(grossreceipts) AS [SumOfgrossreceipts]
FROM tblgross
GROUP BY Custid

[slimjen]

I apologize. I need to make sure I understand. First; I realized I put a space in MaxOfSumOfgross receipts where it should not be. You latest code leaves out the join?? So just so I understand, I can't get the sum and max of grossreceipts field in the same query using a join and grouping?

[pbaldy]

I didn't say that, I just wanted to start simple and then work from there. Did it work? I didn't see you getting a field from the other table anyway, other than the join field.

[slimjen]

Oh. I misunderstood. Yes it did work. I need to include the join tblCust.Custid.

[slimjen]

Thanks so much Paul. I added back the join field by field and it worked fine. There is some difference in the expected results but I think I can resolve it at this point. I will work on this before closing out the thread. Thanks again!

[pbaldy]

Happy to help!

[Xipooo]

I know I'm jumping in late here but in his original query he didn't alias his subquery and that's probably confusing the engine on where it's supposed to grab the data from.

SELECT  tblCust.Custid, qrygross.[grossreceipts], Max(SumOfgrossreceipts) AS [MaxOfSumOfgross receipts]
(SELECT tblCust.Custid, tblgross.grossreceipts, tblCust.Custid, Sum(tblgross.grossreceipts)AS [SumOfgrossreceipts]
FROM tblgross LEFT JOIN tblCust ON tblgross.custid = tblCust.custid
GROUP BY tblCust.Custid, tblgross.grossreceipts, tblCust.costid) as qrygross
FROM tblCust LEFT JOIN qrygross ON qrygross.custid= tblCust.custid;

[pbaldy]

I know I'm jumping in late here but in his original query he didn't alias his subquery and that's probably confusing the engine on where it's supposed to grab the data from.

SELECT  tblCust.Custid, qrygross.[grossreceipts], Max(SumOfgrossreceipts) AS [MaxOfSumOfgross receipts]
(SELECT tblCust.Custid, tblgross.grossreceipts, tblCust.Custid, Sum(tblgross.grossreceipts)AS [SumOfgrossreceipts]
FROM tblgross LEFT JOIN tblCust ON tblgross.custid = tblCust.custid
GROUP BY tblCust.Custid, tblgross.grossreceipts, tblCust.costid) as qrygross
FROM tblCust LEFT JOIN qrygross ON qrygross.custid= tblCust.custid;

True, but I don't think the original subquery would have worked anyway, with multiple fields in the SELECT and the fact that it could return multiple records. It could have worked if it were joined in the FROM clause, but wasn't necessary in either case. I don't think you can join to it the way you have it (it would need to be in the FROM clause, not the SELECT clause). Plus you've left out the comma too.