Hi all,

I have a situation that I can't find a solution to.

I'm trying to run a SELECT query with a special CASE statement as shown below.
What I've shown below doesn't work so I'm hoping someone can shed some light.

I get an error saying: "Invalid use of non-aggregated column".

SELECT
CASE COUNT(PaymentID)
 WHEN (COUNT(PaymentID) > 1) THEN '(Multiple Items)'
 WHEN (COUNT(PaymentID) <= 1) THEN "Transaction"."Description"
END AS Desc,

Payment.Amount, Payment.DateReceived, "Transaction"."Description", Payment.PaymentID, Payment.PaymentMethod
FROM Payment   
INNER JOIN "Transaction" ON (Payment.SaleID = "Transaction".SaleID)   
WHERE (Payment.DateReceived > '2008-05-22 22:18:00')   AND   Payment.PaymentMethod = 'Cheque'  
GROUP BY PaymentID

Any ideas?

Thanks heaps!

Warm regards,
Mike.Ryan

Mike


Try adding Count(PaymentID) into the selected fields list

I just tried

select _Plan,  count(_Plan) ,
case count(_Plan)
when count(_Plan) > 10 then 'xxx'
when count(_Plan) <=10 then 'zzz'
end as alf
from projectdetails
group by _Plan

and it works

Roy Lambert [Team Elevate]

Mike,

<< I'm trying to run a SELECT query with a special CASE statement as shown
below. >>

Your CASE syntax is wrong, it should be:

SELECT
CASE
 WHEN (COUNT(PaymentID) > 1) THEN '(Multiple Items)'
 WHEN (COUNT(PaymentID) <= 1) THEN "Transaction"."Description"
END AS Desc,

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi guys,

Thanks for replying.

I think the problem lies in the second WHEN.  "Transaction"."Description" is a field, and this seems to be the line that trips up.  If I replace this
with a string (e.g., '!'), then it works.

CASE
 WHEN (COUNT(Payment.PaymentID) > 1) THEN '(Multiple Items)'
 WHEN (COUNT(Payment.PaymentID) <= 1) THEN "Transaction"."Description"

In any case, it seems I got my wires crossed anyways  
Turns out this query won't work for me at all because COUNT(Payment.PaymentID) is for the whole query, not the specific GROUP.

That is, if I have the following rows (returned from a JOIN):
PaymentID     Description (from joined table)  Amount
1                   Item One                                 $100
2                   Item Two                                 $25
2                   Item Three                              $25

Then I was hoping to get the following results instead:
PaymentID     Description                              Amount
1                   Item One                                 $100
2                   '(Multiple Items)'                      $50

I hope I've explained this properly.

Any ideas?

Thanks!!

M.


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Mike,

<< I'm trying to run a SELECT query with a special CASE statement as shown
below. >>

Your CASE syntax is wrong, it should be:

SELECT
CASE
 WHEN (COUNT(PaymentID) > 1) THEN '(Multiple Items)'
 WHEN (COUNT(PaymentID) <= 1) THEN "Transaction"."Description"
END AS Desc,

--
Tim Young
Elevate Software
www.elevatesoft.com

"Mike Ryan" <mike.ryan@bigpond.com> wrote in message
news:827CB34B-5F7A-4202-8692-31861801F2D0@news.elevatesoft.com...
>
> I hope I've explained this properly.
>
> Any ideas?
>

I didn't see how it could work, the count needs to be done and grouped ahead
of time, but I'm always open to new ideas

I think you need a script

SELECT ID, COUNT(PAYMENTID) IDS INTO MEMORY\TEMP FROM TABLE GROUP BY ID;
SELECT FIELDS, IF(IDS > 1 "MULTIPLE" ELSE "SINGLE")
FROM TABLE T
JOIN MEMORY\TEMP M ON T.ID = M.ID;

As usual with this type of script, if the memory table can get big, add an
index on ID to the memory table to optimize the join.

Robert