how to write this query??

[ocpdude]

Hi,
I have couple of columns in my table like this
bill_id, date_visit, type, amt
and I am trying to select everything from this table
and ONLY group by bill_id and SUM(amt)..

if I did this it will not work

select bill_id,date_visit,type , sum(amt)
from table_x
group by bill_id

How can I do this in one select statment??

thanks

[samdba]

Originally posted by ocpdude
Hi,
I have couple of columns in my table like this
bill_id, date_visit, type, amt
and I am trying to select everything from this table
and ONLY group by bill_id and SUM(amt)..

if I did this it will not work

select bill_id,date_visit,type , sum(amt)
from table_x
group by bill_id

How can I do this in one select statment??

thanks

Hi,

If for the same bill_id, date_visit,type are same than you can write like this...


select bill_id,date_visit,type , sum(amt)
from table_x
group by bill_id,date_visit,type

Try this and tell me.

[ocpdude]

the problem is date_visit and type are not the same
so I need to be able to ONLY groub by BILL_ID

[marist89]

I'm not sure your logic makes sense, but the query would be something like:

select a.bill_id, a.date_visit, a.type , b.sum_amt
from table_x, 
   (select bill_id, sum(amt) sum_amt 
    from table_x group by bill_id ) b
where b.bill_id = a.bill_id

[ocpdude]

you right marist89, the logic does not make sense.
but that is the data I have to deal with :(

anyway,
thank you very much

[samdba]

Originally posted by ocpdude
you right marist89, the logic does not make sense.
but that is the data I have to deal with :(

anyway,
thank you very much

Hi,

Pl. explain us your business requirements.

I am taking following example for your case.

Table-x

Bill_id Date_visit Type Amt
1 12/10/01 A 100
1 12/11/01 A 200
1 12/12/01 B 100
2 12/01/01 A 100
2 12/01/01 B 200

Now tell me what output you are looking for from above data?

[ocpdude]

Ok,
we are downloading these data from x_site to
our table. the bill_id is suppose to be uniqe. but
whoever enters these data from x_site he/she might
enter the same bill twise with different data(date, type) but same amt.

my job is to only pick one of these duplicate bills (it does
not matter wich bill I pick). the sum(amt) thing was just
an extra thing for me. I realy did not need the sum since
I am only picking just one bill.

so data could look like this

Table-x
Bill_id Date_visit Type Amt
--1 12/10/01 A 100
--2 12/11/01 A 200
--3 12/12/01 B 100
--4 12/01/01 A 100
--5 12/01/01 B 200
--5 12/01/01 J 200

and my job is to pick only one of the bill_id = 5
of course with the rest of the info and records

hope this helps

[ocpdude]

ok,
I have this data in my bill_dl
BILL_ID------ BILL_BARCODE
-----------------------------------
0800034----IPAC0000693
09700007---IPAC0000692
09700007---IPAC0000692
09700008---IPAC0000697
26000037---IPAC0000700

select b.bill_id, a.bill_barcode
from bill_dl a,
(select bill_id
from bill_dl group by bill_id ) b
where b.bill_id = a.bill_id;

but I got the same thing (bill_id = 09700007 is duplicated)

BILL_ID------ BILL_BARCODE
-----------------------------------
0800034----IPAC0000693
09700007---IPAC0000692
09700007---IPAC0000692
09700008---IPAC0000697
26000037---IPAC0000700

any idea ?????

[samdba]

Originally posted by ocpdude
ok,
I have this data in my bill_dl
BILL_ID------ BILL_BARCODE
-----------------------------------
0800034----IPAC0000693
09700007---IPAC0000692
09700007---IPAC0000692
09700008---IPAC0000697
26000037---IPAC0000700

select b.bill_id, a.bill_barcode
from bill_dl a,
(select bill_id
from bill_dl group by bill_id ) b
where b.bill_id = a.bill_id;

but I got the same thing (bill_id = 09700007 is duplicated)

BILL_ID------ BILL_BARCODE
-----------------------------------
0800034----IPAC0000693
09700007---IPAC0000692
09700007---IPAC0000692
09700008---IPAC0000697
26000037---IPAC0000700

any idea ?????

Try This...

select bill_id,bill_barcode,count(bill_id) from bill_dl
group by bill_id,bill_barcode
having count(bill_id) > 1;

I created the test table with your data and above qry worked.

Tell me the result

[svk]

This seems to be the case of eliminating duplicate rows.

But a different thought : If you are sure that the amount is same for two or more entries for the same bill_id, why cann't you do a avg(amount) ? You will get the results.