Thread: distinct doesn't work

stephen wrote:
>
> Hi
>
> I am doing this:
>
> SELECT distinct cc.trader_id, t.credit_card_number, t.expiry_date,
> t.name_on_card,
> sum(to_number(cc.debit_credit,'999999.99')),cc.approval_number,
> t.business_name FROM credit_card_history AS cc INNER JOIN traders AS t
> USING (trader_id) GROUP by cc.trader_id, t.credit_card_number,
> t.expiry_date, t.name_on_card, cc.approval_number, t.business_name
> having sum(to_number(cc.debit_credit,'999999.99')) != 0
>
> And I expect to get one line as I only have I trader-id in my table(s)
> but I get this:
>
> 1493 4321442143314432 09/09 two duck -200 2 Duck Trading Company
>
> 1493 4321442143314432 09/09 two duck 200 5678 2 Duck Trading Company
>
> In fact I don’t actually expect to get any response as the sum should
> have summed to zero.
>
> Does anyone know what I am doing wrong?
>
> Stephen Choularton
>
> 0413 545 182
>
> 02 9999 2226
>
Disctinct is applied to the entire row (unless you have specified "on"
clause). In your case you might need to exclude grouping by
cc.approval_number to get the sum for the certain trader.

At 12:13 PM 7/6/07, stephen wrote:
SELECT
distinct cc.trader_id, t.credit_card_number, t.expiry_date,
t.name_on_card,
sum(to_number(cc.debit_credit,'999999.99')),cc.approval_number,
t.business_name  FROM credit_card_history AS cc INNER JOIN traders
AS t USING (trader_id) GROUP by cc.trader_id, t.credit_card_number,
t.expiry_date, t.name_on_card, cc.approval_number, t.business_name having
sum(to_number(cc.debit_credit,'999999.99')) != 0

And I expect to get one line as I only have I
trader-id in my table(s) but I get this:

1493 4321442143314432 09/09 two duck -200 
2 Duck Trading Company

1493 4321442143314432 09/09 two duck 200 5678 2
Duck Trading Company

In fact I dont actually expect to get
any response as the sum should have summed to zero.

Does anyone know what I am doing wrong?

It's hard to say for sure because to don't have column delimiters in
output; but it kinda looks like the first result is sum of all rows
without an approval_number and the second ro has approval_number
5678