MySQL Math on "join" function

user2292759 Published at Dev

user2292759

Not too sure how to explain this, but, basically I have two tables, and I need to do some math on their values. I am having issues with anything other than addition (which it does automatically).

This is the MySQL statement that I am using.

select snippet_id, count(snippet_id) as cnt
from snippets_likes join snippets_engagement on snippet_id = snippets_engagement.snip_id
group by snippet_id
order by cnt desc

This statements pulls the total number of likes + engagements.

However, I want to have it equal likes + ( engagements/1000 ).

Table construction

CREATE TABLE  `IOTunes`.`snippets_engagement` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `snip_id` int(10) unsigned NOT NULL DEFAULT '0',
  `artist_id` int(10) unsigned NOT NULL DEFAULT '0',
  `snip_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `engagement_type` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6694 DEFAULT CHARSET=latin1;

CREATE TABLE  `IOTunes`.`snippets_likes` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `snippet_id` int(10) unsigned NOT NULL DEFAULT '0',
  `artist_id` int(10) unsigned NOT NULL DEFAULT '0',
  `snippet_like` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=197 DEFAULT CHARSET=latin1;

I have tried so many different formulas, but, cannot get this to work. Any pointers?

Uueerdo

Try this statement:

SELECT snippet_id, SUM(cnt) AS cnt
FROM (
   SELECT snippet_id, COUNT(1) AS cnt
   FROM snippets_likes 
   GROUP BY snippet_id
   UNION
   SELECT snippet_id, COUNT(1)/1000 AS cnt
   FROM snippets_engagement 
   GROUP BY snippet_id
) AS subQ
GROUP BY snippet_id
ORDER BY cnt DESC

or if you insist on a join...

select sl.snippet_id, count(DISTINCT sl.ID) + count(DISTINCT se.ID)/1000 as cnt
from snippets_likes AS sl join snippets_engagement AS se 
on sl.snippet_id = se.snip_id
group by snippet_id
order by cnt desc

I wouldn't recommend the join though, you'll only get values for "snippets" that have at least one "like" and one "engagement".

Collected from the Internet

Please contact [email protected] to delete if infringement.