Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help with SQL query

Author  Topic 

mehfuz
Starting Member

3 Posts

Posted - 2013-07-20 : 01:36:20
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)
);

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

mehfuz
Starting Member

3 Posts

Posted - 2013-07-20 : 02:10:47
I forgot to mention that it has a composite primary key as follows:
(msisdn, imei)
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-07-20 : 04:19:12
Hello



with mastertab
as
(
select 'aa' as OP, '0191' as msisdn , '111222333' as imei union all
select 'aa', '0191', '111222444' union all
select 'aa', '0192', '111222333' union all
select 'aa', '0192', '111222444' union all
select 'aa', '0192', '111222555' union all
select 'aa', '0193', '111222333' union all
select 'bb', '0171', '222222333' union all
select 'bb',' 0171', '222222444' union all
select 'bb', '0172', '222222444' union all
select 'cc', '0152', '333222444')

select op,
count(distinct imei) as [xxx]
from
(
select op ,imei
,count(imei) over(partition by op,imei) rw
from mastertab
)A
where rw>1
group by op


output :

op xxx
aa 2
bb 1



to display the row with 0

select op,
count(distinct imei)-1 as [xxx]
from
(
select op ,imei
,count(imei) over(partition by op,imei) rw
from mastertab
)A
where rw>=1
group by op




S


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-07-20 : 04:19:59
[code]

select A.op
, count(distinct A.imei) - 1 as NoImei
from
(
select op,imei
,ROW_NUMBER() over(partition by op, imei order by op, imei) rw
from mastertab)A
where A.rw>=1
group by A.op

[/code]


S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-20 : 09:02:19
[code]-- SwePeso
SELECT OP,
SUM(CASE WHEN Items > 1 THEN 1 ELSE 0 END)
FROM (
SELECT OP,
COUNT(*) AS Items
FROM mastertab
GROUP BY OP,
IMEI
) AS d
GROUP BY OP[/code]

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-20 : 09:11:03
[code]-- SwePeso
SELECT OP,
SUM(Items)
FROM (
SELECT OP,
SIGN(COUNT(*) - 1) AS Items
FROM mastertab
GROUP BY OP,
IMEI
) AS d
GROUP BY OP;[/code]

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

mehfuz
Starting Member

3 Posts

Posted - 2013-07-20 : 09:43:13
Thanks SwePeso brother. Your later solution worked for me.

Cheers for you.
Go to Top of Page
   

- Advertisement -