Help with SQL query

Question

Post reply

Help with SQL query

mehfuz.khaled

SSC Veteran

Points: 220
More actions
July 20, 2013 at 12:00 am

#296649

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

mehfuz.khaled SSC Veteran Points: 220 More actions · Answer 1

I forgot to mention that it has a composite primary key as follows:

(msisdn, imei)

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

First thing is your table structure is incorrect..

You have specified op as INT but you are inserting character values in that column

aa, 0191, 111222333

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

you can try this-

;WITH CTE AS

(

select op,imei, DENSE_RANK() over (order by op,imei) rankno

FROM mastertab

)

select c.op, COUNT(distinct rankno) - 1 cnt

FROM CTE c

GROUP BY c.op

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

mehfuz.khaled SSC Veteran Points: 220 More actions · Answer 4

mehfuz.khaled

SSC Veteran

Points: 220

July 20, 2013 at 1:41 am

#1634236

Op is also char. Sorry about that.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

mehfuz.khaled (7/20/2013)
Op is also char. Sorry about that.

Change the datatype of Op to CHAR and run the query that I posted... Hope it will work for you

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

kapil_kk (7/20/2013)
you can try this-
;WITH CTE AS
(
select op,imei, DENSE_RANK() over (order by op,imei) rankno
FROM mastertab
)
select c.op, COUNT(distinct rankno) - 1 cnt
FROM CTE c
GROUP BY c.op

Try with this data and see what happens..

drop table mastertab

CREATE table mastertab(

op CHAR(2),

msisdn VARCHAR (12),

imei VARCHAR (20)

);

INSERT INTO mastertab

(op,msisdn,imei)

SELECT 'aa','0191','111222333' 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 'aa','0193','111222334' UNION ALL --added

SELECT 'aa','0193','111222335' UNION ALL --added

SELECT 'aa','0193','111222336' UNION ALL --added

SELECT 'aa','0193','111222337' UNION ALL --added

SELECT 'aa','0193','111222338' UNION ALL --added

SELECT 'bb','0171','222222333' UNION ALL

SELECT 'bb','0171','222222444' UNION ALL

SELECT 'bb','0172','222222444' UNION ALL

SELECT 'cc','0152','333222444'

;WITH CTE AS

(

select op,imei, DENSE_RANK() over (order by op,imei) rankno

FROM mastertab

)

select c.op, COUNT(distinct rankno) - 1 cnt

FROM CTE c

GROUP BY c.op

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

mehfuz.khaled (7/20/2013)
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

First, you're new here. You'll get a whole lot more coded, tested answers if you follow the simple advice offered in the first link in my signature line below.

Second, what do you want to be returned if the data is like this?

aa, 0191, 111222333

aa, 0191, 111222444

aa, 0192, 111222333

aa, 0192, 111222444

aa, 0193, 111222555

aa, 0194, 111222555

aa, 0195, 111222556

aa, 0196, 111222556

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mehfuz.khaled SSC Veteran Points: 220 More actions · Answer 8

Thanks Jeff for your response.

For the inputs that you have given, my expected output would be as follow:

Op imei_count

-- -----------

aa 4

Thanks and regards,

Mehfuz

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

SELECT op, SUM(CASE WHEN dupes > 1 THEN 1 ELSE 0 END)

FROM (

SELECT op, imei, dupes = COUNT(*)

FROM #mastertab

GROUP BY op, imei

) d

GROUP BY op

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 10

ChrisM@Work (7/22/2013)
SELECT op, SUM(CASE WHEN dupes > 1 THEN 1 ELSE 0 END) FROM ( SELECT op, imei, dupes = COUNT(*) FROM #mastertab GROUP BY op, imei ) d
GROUP BY op

can you please explain your query... I am not getting outer select statement... SUM(CASE WHEN dupes > 1 THEN 1 ELSE 0 END)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 11

ChrisM@Work (7/22/2013)
SELECT op, SUM(CASE WHEN dupes > 1 THEN 1 ELSE 0 END) FROM ( SELECT op, imei, dupes = COUNT(*) FROM #mastertab GROUP BY op, imei ) d
GROUP BY op

thanks Chris....

got you query now

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/