Databases
StackOverflow2013
Postsdbo
POHow to return the number of records in a join for a given month?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to return the number of records in a join for a given month?
    text
    copied!<p>I have the following simple schema:</p> <pre><code>CREATE TABLE POSTS ( ID INT NOT NULL, DATE DATE NOT NULL, [Other stuff omitted] ); CREATE TABLE TOPICS ( ID INT NOT NULL, [Other stuff omitted] ); CREATE TABLE THETA ( POST_ID INT NOT NULL, TOPIC_ID INT NOT NULL, WEIGHT FLOAT NOT NULL ); </code></pre> <p>I have a query to sum the WEIGHT in THETA across all posts, grouped by date and topic ID:</p> <pre><code>SELECT THETA.TOPIC_ID as TopicID, POSTS.DATE as Date, SUM(THETA.WEIGHT) as Value FROM POSTS INNER JOIN THETA WHERE THETA.POST_ID=POSTS.ID GROUP BY YEAR(POSTS.DATE), MONTH(POSTS.DATE), TopicID; </code></pre> <p>This works as expected, giving results like this:</p> <pre><code>+---------+------------+---------------------+ | TopicID | Date | Value | +---------+------------+---------------------+ | 0 | 2008-08-19 | 350.4930010139942 | | 0 | 2008-09-18 | 1745.5010008439422 | | 0 | 2008-10-03 | 1468.824001269415 | | 0 | 2008-11-25 | 1079.579000659287 | | 0 | 2008-12-11 | 1070.3860008455813 | | 0 | 2009-01-24 | 1453.3730010837317 | | 0 | 2009-02-20 | 1139.2920009773225 | | 1 | 2008-08-19 | 288.09700035490096 | | 1 | 2008-09-22 | 1307.5790000930429 | | 1 | 2008-10-16 | 1050.1739999558777 | | 1 | 2008-11-11 | 868.2280002105981 | | 1 | 2008-12-18 | 897.6830000579357 | | 1 | 2009-01-12 | 1148.5619999151677 | | 1 | 2009-02-12 | 858.0710002686828 | | 2 | 2008-08-19 | 415.83300026878715 | ... </code></pre> <p>However, I would like to normalize the Value by the number of posts in that month. For example, if there were 100 posts in the month <code>2008-08-19</code>, then the first result row would have a Value of 3.50493 and the eight result row would have a Value of 2.88097. The challenge is that the number of posts varies per month, so I'm not quite sure what to do. Any ideas?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload