Sql not showing correct result
I have the following tables in mySql.
blog
Field Type
---------- ------------
id int(11)
name varchar(255)
user_id int(11)
share int(14)
user_blog_analytics
Field Type
----------- ------------
id int(11)
blog_id int(11)
ip varchar(255)
impressions int(11)
date date
user_profile
Field Type
----------- ------------
id int(11)
user_id int(11)
description text
share int(14)
user_profile_analytics
Field Type
----------- ------------
id int(11)
user_id int(11)
ip varchar(255)
impressions int(11)
date date
users
Field Type
----------- ------------
id int(11)
email varchar(255)
I need a query that gives me the total blog shares for each user from the table blog
, the total profile shares of each user from the table user_profile
, the total blog views from yesterday i.e. from the table user_blog_analytics
, all time views per profile from the table user_profile_analytics
.
I created a query, but it didn't give me the expected results, it only gives me small results.
SELECT a.user_id, COUNT(DISTINCT b.ip) AS blog_view_count, a.share AS blog_share_count, c.share AS profile_share_count, COUNT(DISTINCT d.ip) AS user_profile_view
FROM blog AS a
JOIN user_blog_analytics AS b ON b.blog_id=a.id
JOIN user_profile AS c ON c.user_id=a.user_id
JOIN user_profile_analytics AS d ON d.user_id=c.user_id
JOIN users AS e ON e.id=a.user_id
WHERE DATE_SUB(CURDATE(), INTERVAL 1 DAY) = b.date AND e.role_id=2
GROUP BY a.id;
When I ran this query it only gave me one result, but when I manually checked the tables it should give me at least 2 results. Tell me where I am going wrong and how can I get the result by changing this query.
source to share
Try the following:
SELECT u.id, u.email, b.blog_share_count, b.blog_view_count,
up.profile_share_count, upa.user_profile_view
FROM users u
LEFT JOIN (SELECT b.user_id, SUM(b.share) AS blog_share_count, COUNT(DISTINCT b.ip) AS blog_view_count
FROM blog b
LEFT JOIN user_blog_analytics AS uba ON uba.blog_id = b.id AND DATE_SUB(CURDATE(), INTERVAL 1 DAY) = uba.date
GROUP BY b.user_id
) b ON u.id = b.user_id
LEFT JOIN (SELECT up.user_id, SUM(up.share) AS profile_share_count
FROM user_profile up
GROUP BY up.user_id
) up ON u.id = up.user_id
LEFT JOIN (SELECT up.user_id, COUNT(DISTINCT up.ip) AS user_profile_view
FROM user_profile_analytics up
GROUP BY up.user_id
) upa ON u.id = upa.user_id
source to share
Not an answer, but something to think about ...
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table(i14 INT(14),i4 INT(4));
INSERT INTO my_table VALUES (123456789012345,123456789012345);
SELECT * FROM my_table;
+------------+------------+
| i14 | i4 |
+------------+------------+
| 2147483647 | 2147483647 |
+------------+------------+
So the numbers in parentheses don't do anything for ya!
source to share