distal-attribute
distal-attribute
distal-attribute
distal-attribute

Help with SQL Query

User, date Message
Written by mehfuz
1 year ago
Category: General
1 posts since Sat, 20 Jul 13
Hello all, I am not sure whether this is the right thread to post this or not.

Anyways, I have one table as follows:

table mastertab(
op INT,
msisdn VARCHAR (12),
imei VARCHAR (20)
);

It has a composite primary key as (msisdn, imei)

Sample values that I have are as follows:

aa, 0191, 111222333
aa, 0191, 111222444
aa, 0192, 111222333
aa, 0192, 111222444
aa, 0192, 111222555
aa, 0193, 111222333
bb, 0171, 222222333
bb, 0171, 222222444
bb, 0172, 222222444
cc, 0152, 333222444

Now, i want the output to show as (op, imei_count) set where imei_count displays the total number of duplicate imei and groups it by op. Expected output is as follows:

op imei_count
-- ----------
aa 2
bb 1
cc 0

Any help in this regard will highly be appreciated.

Thanks,

Mehfuz
Written by ansgar
1 year ago
5023 posts since Fri, 07 Apr 06
This is a simple aggregation query:

SELECT op, imei, COUNT(*) AS num
GROUP BY imei
ORDER BY num DESC

 

Please login to leave a reply, or register at first.