MYSQL selects a counter for each unique row, even if count is zero
I'm having problems writing select where I can get multiple Counts for each unique row, I searched the whole net and wrote select but didn't work the way I want. So I have two banner tables 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 the userId declaration. I want to count different IP addresses for each banner separately. My code doesn't work because it is counting. The first banners are different (count (Ip)) for each banner, I mean almost always the number is the same. Can anyone help fix my request? Or show me a different approach. don't worry about the columns of counts and clicks, they are in the table banners
so they don't calculate anything, just get them. Sorry for my English.
source to share
This is the case when you want to perform an aggregation operation (COUNT ... GROUP BY) in a subquery and then do your join. The subquery looks like this and gets the number of different IP values for each combination of user ID and banner 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 this as if it were a virtual table into your table banner
and use it WHERE
to select the user id you want. ( 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
Note that the MySQL query planner is usually smart enough to execute a statement efficiently WHERE
even in a subquery.
Edit Finally, the normal inner JOIN
suppresses lines that don't match the ON condition. To store every row of the table banner
, 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
Note the use SELECT ... IFNULL
to put zeros in place of zeros for rows that are not in the table BannerHistory
.
Tip: Create a personal indentation style for the request code so you can see the structurein a structured query language. The extra 30 seconds it takes to indent, each request will indent itself hundreds of times as you maintain your code.
source to share
Try using the following 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 that in your schema, storing the userId in bannerhistory is redundant if the banner table has this link id vs user, so there should only be a combination of bannerId vs ip in bannerhistory.
source to share