July 20, 2013 at 12:00 am
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
July 20, 2013 at 12:12 am
I forgot to mention that it has a composite primary key as follows:
(msisdn, imei)
July 20, 2013 at 1:16 am
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/
July 20, 2013 at 1:28 am
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/
July 20, 2013 at 1:41 am
Op is also char. Sorry about that.
July 20, 2013 at 2:42 am
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/
July 20, 2013 at 5:10 pm
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
Change is inevitable... Change for the better is not.
July 20, 2013 at 5:33 pm
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
Change is inevitable... Change for the better is not.
July 21, 2013 at 8:25 pm
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
July 22, 2013 at 5:54 am
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
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
July 22, 2013 at 7:02 am
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/
July 22, 2013 at 7:13 am
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/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply