Novice getting error on query - please critique

[mo]

I made this query:
select CONSIGNMENT_CODES.CONSIGNMENT_CODE,
sum(INVC_DETAIL.QTY_SHIP*INVC_DETAIL.UNIT_PRICE)
from INVC_DETAIL, CONSIGNMENT_CODES
where INVC_DETAIL.CNC_AUTO_KEY=CONSIGNMENT_CODES.CNC_AUTO_KEY
group by month(INVC_DETAIL.INVOICE_DATE)
order by INVC_DETAIL.CNC_AUTO_KEY, INVC_DETAIL.INVOICE_DATE;

And get the following error:
You tried to execute a query that does not include the specified
expression 'CONSIGNMENT_CODE' as part of an aggregate function.

Any explanations or suggestions would be greatly appreciated.

-Mo

 

[Jerry Whittle]

CONSIGNMENT_CODES.CONSIGNMENT_CODE needs to be part of the GROUP BY or do
some aggregrate function like sum, avg, min, max, etc.

 

[mo]

Can you have multiple fields for 'order by'?
If so, what would that syntax look like?

(I tried adding it, but got another error.)

-Mo

 

[Van T. Dinh]

You got 2 Fields in the ORDER BY clause of the original SQL you posted???

Basically. you can use:

.... ORDER BY Field1 ASC, Field2 ASC, Field3 DESC, ..., FieldX ASC

Since ASC (ASCendingly) is the default sort, you don't need to use ASC but
you do need to use DESC if you want to sort descendingly ...

 

[mo]

Oh, my mistake!
I meant to ask whether you can GROUP BY multiple fields.
I tried it, and thought that I was getting the same error, but it was
in fact calling out for a different field in the aggregate function.

By the help you guys provided, I was able to get it going.

Thanks-a-bunch,
-Mo

 

[mo]

Well, if more support is available, I could actually use some help
tweaking it a little.

This is the code I have now:
SELECT INVC_DETAIL.INVOICE_DATE, CONSIGNMENT_CODES.CONSIGNMENT_CODE,
sum(INVC_DETAIL.QTY_SHIP*INVC_DETAIL.UNIT_PRICE)
FROM INVC_DETAIL, CONSIGNMENT_CODES
WHERE INVC_DETAIL.CNC_AUTO_KEY=CONSIGNMENT_CODES.CNC_AUTO_KEY
GROUP BY month(INVC_DETAIL.INVOICE_DATE),
CONSIGNMENT_CODES.CONSIGNMENT_CODE, INVC_DETAIL.INVOICE_DATE;

The results I REALLY want would be as such:
<u>Month Code Amt</u>
Jan, 06 B-001 $200.00
Jan, 06 B-015 $150.00
Feb, 06 B-001 $75.00
Feb, 06 C-017 $500.00

Right now, though, it is returning a record per date.

Any advise?
-Mo

 

[Van T. Dinh]

Not sure as I lost track of the thread but try:

SELECT Format(INVC_DETAIL.INVOICE_DATE, "mmm,\ yy"),
CONSIGNMENT_CODES.CONSIGNMENT_CODE,
sum(INVC_DETAIL.QTY_SHIP*INVC_DETAIL.UNIT_PRICE)
FROM INVC_DETAIL, CONSIGNMENT_CODES
WHERE INVC_DETAIL.CNC_AUTO_KEY=CONSIGNMENT_CODES.CNC_AUTO_KEY
GROUP BY Format(INVC_DETAIL.INVOICE_DATE, "mmm,\ yy"),
CONSIGNMENT_CODES.CONSIGNMENT_CODE
ORDER BY Year(INVC_DETAIL.INVOICE_DATE) ASC,
Month(INVC_DETAIL.INVOICE_DATE) ASC;

However, that may not be the most efficient SQL as functions Format(),
Month() and Year() needs to be called for each Record ...

 

[mo]

First, I'd like to say thanks.

Secondly, I'm basically getting that same old error again:
You tried to execute a query that does not include the specified
expression 'Year(INVC.DETAIL.INVC_DATE)' as part of an aggregate
function.

If the past holds true for the future, it is going to give this error
for each item used in ORDER BY.
What's the best way to address this?

-Mo