After the greater than or less than, why is the argument month? What is that doing?
This WHERE clause compares all the values in ‘start_month’ and ‘end_month’ columns from newspaper table with the values in ‘month’ column from months table.
It is used to select only the rows with the months when the person was actually subscribed.
The rows inside the red rectangle will not be selected in this case.
Actually i think and i stand to be corrected but the CROSS JOIN function should be accompanied by a WHERE clause in order to give a more meaningful result.
I understand this, but what query would I use if I wanted to determine the total number of items? Meaning, what clause could single out each clothing item in a joint –or cross joint– table in order to get the total amount of distinct rows, as in 3 + 2 + 6 = 11? I’ve tried DISTINCT to not much avail.
NO, it’s qualifying the current month it is querying.
If the current month in the process (month) is
(greater than the start month
AND
lesser than the end month, )
THEN
the current month being compared
is WHILE the subscription is active.
Hi, did you mean "…the total amount of distinct rows, as in 1 * 2 * 6 = 12" to count all matches for one particular color of shirt?
Or try to describe your issue a little bit further, maybe with some code example with unknown for you parts.
Because now i’m prone to think that you should use COUNT clause or GROUP BY, but can’t help more due to lack of information.
You would not be using CROSS JOIN, but rather UNION function. This means that when you add all the entries from the second table into the first, they will be added as if separate entries. From there you can use aggregate it using COUNT().
CROSS JOIN creates combination of all the entries in the second table with the first table.
Can someone walk through the logic here in plane english. I actually get the bottom part but am struggling to understand the first 3 lines given Month is a column in Months (not newspaper) but its Selecting FROM newspaper?
SELECT month,
COUNT(*) AS ‘subscribers’
FROM newspaper
CROSS JOIN months
WHERE start_month <= month
AND end_month >= month
GROUP BY month;
Since you CROSS JOIN months, your query will have access to that ‘month’ column as well. The way I understand it, it then selects all of that from your new crossed-joined table that contains everything.
From the point of process steps select is merely command to display results of computation, so literally joins are executed first providing a table results to memory from which you will select necessary fields, so select comes the last logical step in the process…more like a print command
I cannot get any result by applying “MAX(COUNT())", do I need to create another table to host the previous query result, then apply "MAX(COUNT())” on this newly created table?
@moonmullins The whole aim of this code is to count how many people had ACTIVE subscriptions during each month of the year. month is a number 1,2,3…,12 from the months table. The WHERE clause helps to filter the results to get those ACTIVELY subscribed in each month.
If we wanted to know how many new subscribers were added each month, we’d write
WHERE start_month = month