SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help with SQL query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mehfuz
Starting Member

Bangladesh
3 Posts

Posted - 07/20/2013 :  01:36:20  Show Profile  Reply with Quote
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

Bangladesh
3 Posts

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

stepson
Constraint Violating Yak Guru

Romania
434 Posts

Posted - 07/20/2013 :  04:19:12  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
434 Posts

Posted - 07/20/2013 :  04:19:59  Show Profile  Reply with Quote


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




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

Sweden
30265 Posts

Posted - 07/20/2013 :  09:02:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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


N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 07/20/2013 09:06:50
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/20/2013 :  09:11:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- SwePeso
SELECT		OP,
		SUM(Items)
FROM		(
			SELECT		OP,
					SIGN(COUNT(*) - 1) AS Items
			FROM		mastertab
			GROUP BY	OP,
					IMEI
		) AS d
GROUP BY	OP;


N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 07/20/2013 09:11:25
Go to Top of Page

mehfuz
Starting Member

Bangladesh
3 Posts

Posted - 07/20/2013 :  09:43:13  Show Profile  Reply with Quote
Thanks SwePeso brother. Your later solution worked for me.

Cheers for you.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000