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.
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?
COUNT(*) AS ‘subscribers’
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
@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