mysql count having distinct

Question

Hi im looking to get how many users that use one app use that app

╔════════════════════════════════════════════════════════════════════════╗
║    username  clientname     date         time          publishedapp    ║
╠════════════════════════════════════════════════════════════════════════╣
║    akirk   hplaptop1   30/07/2015   8:42:30.04    PB Desktop service   ║
║     john     dellPC1   27/07/2015   9:41:30.04    desktop@Work2-1      ║
║     john     dellPC1   27/07/2015   9:41:30.04    Word 2013            ║
║     karl delllaptop2   27/07/2015   9:40:21.00    Chrome               ║
║     karl delllaptop2   27/07/2015   9:40:21.00    Desktop with acrobat ║
║     jdoe       HPPC1   27/07/2015   9:40:15.00    Powerplan            ║
║     mrt        P2000   31/02/2015   10:03.20      PB Desktop service   ║
╚════════════════════════════════════════════════════════════════════════╝

I would be specting something like this:

PB DEsktop service: 2
Powerplan: 1

I've managed to get

PB DEsktop service: 2
desktop@Work2-1: 1
Desktop with acrobat: 1
Chrome: 1
Word 2013: 1
Powerplan: 1

with this query:

SELECT publishedapp, COUNT(DISTINCT username) as cnt
FROM tbl_name
GROUP BY publishedapp
ORDER BY cnt DESC

how many users that use a single app use that app? look at what im specting... I dont know how to query this... — maco1717
– maco1717, Commented Aug 27, 2015 at 10:22

Madhivanan · Accepted Answer · 2015-08-27 10:22:58Z

3

Try this

SELECT publishedapp, COUNT(*) as cnt
FROM 
(
select username from tbl_name 
group by username
having count(*)=1
) as t1 inner join tbl_name as t2
on t1.username=t2.username
GROUP BY publishedapp
ORDER BY cnt DESC

answered Aug 27, 2015 at 10:22

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

maco1717 Over a year ago

this is returning interesting values, but not consistent with my data... :S id like to know if there is a way to get the usernama back to contrast and see what is what im getting from here exactly, thanks.

Madhivanan Over a year ago

You can add username in the first select as well as in the last group by clause

maco1717 Over a year ago

dont understand, here is what i tried

SELECT publishedapp, username, COUNT(*) as cnt, FROM ( select username from tbl_name group by username having count(*)<=1 ) as t1 inner join tbl_name as t2 on t1.username=t2.username GROUP BY publishedapp, username ORDER BY cnt DESC

Madhivanan Over a year ago

Yes that will list out all users

Gordon Linoff · Accepted Answer · 2015-08-27 12:20:32Z

1

Here is one method that doesn't require a join:

select publishedapp, count(*) as NumberOfUsers
from (select username, min(publishedapp) as publishedapp
      from table t
      group by username
      having count(*) = 1
     ) u
group by publishedapp
order by count(*) desc;

If a user only has one app, then the minimum will be that app.

If a user could have an app multiple times (and you still want them), then change the count(*) in the subquery to count(distinct publishedapp).

edited Aug 27, 2015 at 12:20

answered Aug 27, 2015 at 10:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

maco1717 Over a year ago

andby subquery you mean the HAVING bit?

Gordon Linoff Over a year ago

@maco1717 . . . The query returns the number of users using the app. The subquery is in the FROM clause.

maco1717 Over a year ago

Im using PHP so I need a variable to read the "number of users" what would be the "variable name" on my query I can identify it as I can name it with AS would it be the u at the end of the from clause?

Gordon Linoff Over a year ago

@maco1717 . . . Just give the column whatever name you want it to have.

Bulat · Accepted Answer · 2015-08-27 10:45:25Z

0

You can achieve this with EXISTS:

SELECT t.publishedapp, COUNT (DISTINCT t.username)
FROM tbl_name t
  WHERE NOT EXISTS(
               SELECT * 
               FROM tbl_name 
               WHERE publishedapp = t.publishedapp and 
                     username = t.username AND
                     datetime != t.datetime) -- or use ID if you have it
GROUP BY publishedapp
ORDER BY cnt DESC

answered Aug 27, 2015 at 10:45

Bulat

6,9992 gold badges32 silver badges54 bronze badges

2 Comments

maco1717 Over a year ago

this is not working for me, what is datetime? i suppose both field concatenated... wouln't I have to concat them?

Bulat Over a year ago

yes, it is combination of two fields, ideally you would have ID field in your table that you can use for comparison.

Collectives™ on Stack Overflow

mysql count having distinct

3 Answers 3

4 Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related