MYSQL select count for every unique row even if count is zero

Question

I've trouble writing select where I can get multiple Counts for every unique row, I've searched whole web and wrote select but it's not working the way I want. So, I have two tables banners and bannerhistory

banners
Id  UserId BannerName
167 35      Polly
168 35      Joke
169 21      Kim
170 35      Buck
................
BannerHistory
BannerId  UserId  IP
167       35      123asd123
167       35      123asd123
168       35      BBB123sss
.......................

So my query is:
SELECT
banners.Banner_Name,
banners.Counter,
banners.ClickCounter,
COUNT(DISTINCT(bannerhistory.Ip)) 
FROM
 bannerhistory 
  INNER JOIN
  banners 
  ON bannerhistory.BannerId = banners.ID 
  WHERE
   bannerhistory.BannerId IN 
   (
      SELECT
     bannerhistory.BannerId 
    FROM
     bannerhistory 
      WHERE
     bannerhistory.UserId = $ UserId 
       )
      GROUP BY
       banners.Banner_Name

I want to get all bannerNames for userId ad I want to count distinct IP-s for each banner separately. My code is not working cause in count it gets First banners distinct(count(Ip)) for each banner I mean almost always count is the same. could anyone help to correct my query? Or show me different approach. don't worry about counter and clickcounter columns, they are in banners table nothing to calculate there, just get them as well. Sorry for my English.

abhinsit · Accepted Answer · 2017-06-17 10:57:44Z

2

Try and use below query:

Select b.Id,b.UserId,h.ips_count from banners b inner join 
(select BannerId,count(distinct ip) as ips_count 
from bannerhistory group by BannerId) h on b.Id = h.BannerId

Note/Suggestion:
I think in your schema, keeping userId in bannerhistory is redundant, in case banners table itself have this reference of id vs user, So in bannerhistory, there should be only bannerId vs ip combination.

answered Jun 17, 2017 at 10:57

abhinsit

3,2574 gold badges23 silver badges26 bronze badges

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

1 Comment

temo Over a year ago

Thank you for your answer, I keep userId in bannerhistory for different purposes but thank you for additional tip. Thing is, I've changed your code to get info for one userId:

Select b.Id,b.UserId,h.ips_count from banners  b  inner join  (select BannerId,count(distinct ip) as ips_count  from bannerhistory WHERE bannerhistory.UserId = 38  group by BannerId) h on b.Id = h.BannerId

. I don't get all the banners, My fault I didn't mention not all banners are in bannerhistory, but they might appear. Or maybe I've changed it wrong.

O. Jones · Accepted Answer · 2017-06-17 11:42:30Z

This is a case where you want to do the aggregate (COUNT ... GROUP BY) operation in a subquery, then do your join. The subquery looks like this, and gets the number of distinct IP values for each combination of user and banner id in your history table. (http://sqlfiddle.com/#!9/c502bb/3/0)

            SELECT BannerId, UserId, COUNT(DISTINCT IP) BannerCount
              FROM bannerHistory
             GROUP BY BannerId, UserId

Then you can join that, as if it were a virtual table, to your banner table, and use WHERE to select the desired user id. (http://sqlfiddle.com/#!9/c502bb/2/0)

SELECT b.Id, B.UserId, b.BannerName, c.BannerCount 
  FROM banner B
  JOIN (
            SELECT BannerId, UserId, COUNT(DISTINCT IP) BannerCount
              FROM bannerHistory
             GROUP BY BannerId, UserId
       ) C ON B.Id = c.BannerId AND B.UserId = C.UserId
 WHERE B.UserId = 35

Notice that the MySQL query planner is generally smart enough to deal with the WHERE clause efficiently even in the subquery.

Edit Finally, an ordinary inner JOIN suppresses the rows that don't match the ON condition. To keep every row of your banner table you need to use LEFT JOIN. (http://sqlfiddle.com/#!9/c502bb/4/0)

SELECT b.Id, B.UserId, b.BannerName, IFNULL(c.BannerCount, 0) BannerCount 
  FROM banner B
   LEFT JOIN (
            SELECT BannerId, UserId, COUNT(DISTINCT IP) BannerCount
              FROM bannerHistory
             GROUP BY BannerId, UserId
       ) C ON B.Id = c.BannerId AND B.UserId = C.UserId
 WHERE B.UserId = 35

Notice the use of SELECT ... IFNULL to put zeros in place of nulls for the rows missing from the BannerHistory table.

Pro tip: Develop a personal style of indenting your query code so you can see the structure in structured query language. The extra 30 seconds it takes to indent each query will repay themselves hundreds of times over as you maintain your code.

Thank you For the great answer and additional tips, Works like a charm, Didn't know about IFNULL

Collectives™ on Stack Overflow

MYSQL select count for every unique row even if count is zero

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related