Help with SQL query - SQL Server Forums

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)

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

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

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"

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"

mehfuz
Starting Member

3 Posts

Posted - 2013-07-20 : 09:43:13

Thanks SwePeso brother. Your later solution worked for me.

Cheers for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources