I have tried this below query in MySQL but not working.
select count(*)
from (
select count distinct(RADL_REQUEST_MSISDN)
from rbt_activation_details_log
where RADL_ACTIVE ='A'
group by RADL_REQUEST_MSISDN
);
4 Answers
select count(distinct column_whose_distinct_values_you_want_to_count)
from rbt_activation_details_log
where RADL_ACTIVE ='A'
group by RADL_REQUEST_MSISDN
You're grouping and counting on the same column, so this will always give you 1 as result.
EDIT:
Then simply omit the group by clause
select count(distinct RADL_REQUEST_MSISDN)
from rbt_activation_details_log
where RADL_ACTIVE ='A'
2 Comments
fancyPants
Thanks, didn't notice that on fast reading. Edited my answer accordingly.
Dibakar Debnath
I need the Total count . for example in oracle this query is working fine and giving the count select count(*) from ( select count(distinct tariff_id_n)from TARIFF_PLANS WHERE STATUS_N=1 group by TARIFF_ID_N); output 126
SELECT COUNT(DISTINCT RADL_REQUEST_MSISDN)
FROM rbt_activation_details_log
WHERE RADL_ACTIVE = 'A'
GROUP BY RADL_REQUEST_MSISDN;
Comments
By your query:
select count(*)
from
(select count distinct(RADL_REQUEST_MSISDN)
from rbt_activation_details_log
where RADL_ACTIVE ='A' group by RADL_REQUEST_MSISDN);
Appears as you want retrieve all distinct count not grouped by radl_request_msisdn. If so than you rewrite your query in this way:
select count distinct(RADL_REQUEST_MSISDN)
from rbt_activation_details_log
where RADL_ACTIVE ='A';
Instead if you want to grouped, the query will be:
select count distinct(RADL_REQUEST_MSISDN)
from rbt_activation_details_log
where RADL_ACTIVE ='A' group by RADL_REQUEST_MSISDN;
4 Comments
Dibakar Debnath
i m not getting the desired result . i need the total count. these query will list out all the columns and i do not want.My query working fine in oracle but for mysql how i need to modify plz suggest
Joe Taras
@user2696461: Dear, my queries return only numbers! The first returns a distinct count of all rbt_activation_details_log; the second returns a distinct count depends by rbt_activation_details_log. Using ob subquery to get count of count is not always correct.
Dibakar Debnath
Yes joe you are correct and query giving output correctly. There are around 100000 count if i do not use distinct. i want the distinct count. i do not want to list all those count
Joe Taras
@user2696461: Ok, so you will use my first query ;). Have a nice day
SELECT Count(UNIQUE(radl_request_msisdn))
FROM rbt_activation_details_log
WHERE radl_active = 'A'
GROUP BY radl_request_msisdn
1 Comment
Susie
@user2696461 I have edited the answer according to your table info. This should do it.
select count distinct(RADL_REQUEST_MSISDN) from rbt_activation_details_log where RADL_ACTIVE ='A' group by RADL_REQUEST_MSISDN?