I track visitors to my app and CD pages by storing each visit in a log file. I can get the total visits for CDs with this code. I use a RIGHT OUTER JOIN because I only want to count visits for CDs. They are in the product table, so if I use a RIGHT OUTER JOIN I will not get any rows that are from the apps.
\n
\nSELECT product.name, product_id, COUNT(*) FROM log \nRIGHT OUTER JOIN product\nON product.id = log.product_id\nGROUP BY product_id\nORDER BY COUNT(*) DESC\n<\/code><\/pre><\/p>\nTotal visits for apps is from this code. I need to subtract 100 from the product_id because apps and CDs both start their ids with 1. To differentiate them, I add 100 to apps before putting them into the log table.
\n
\nSELECT app.name, product_id, COUNT(*) FROM log \nRIGHT OUTER JOIN app\nON app.id = (log.product_id - 100)\nGROUP BY product_id\nORDER BY COUNT(*) DESC\n<\/code><\/pre><\/p>\nTo combine the two results into one table, I use a UNION. I renamed the first column of each table so that combined table will display the column header as name. I want to sort on COUNT(*) but that isn\u2019t available in the UNION so I rename COUNT(*) to count. Now I have a named column in the resulting table and I can sort on it.
\n
\nSELECT app.name AS name, product_id, COUNT(*) AS count\nFROM log \nRIGHT OUTER JOIN app ON app.id = ( log.product_id -100 ) \nGROUP BY product_id\n\nUNION \n\nSELECT product.name AS name, product_id, COUNT(*) AS count\nFROM log \nRIGHT OUTER JOIN product ON product.id = log.product_id\nGROUP BY product_id\nORDER BY count DESC\n<\/code><\/pre><\/p>\nOne caveat. Because I used RIGHT OUTER JOINs, there may be rows in the log table that were not summarized. In fact there are several from the landing page that are not counted.<\/p>\n","protected":false},"excerpt":{"rendered":"
I track visitors to my app and CD pages by storing each visit in a log file. I can get the total visits for CDs with this code. I use a RIGHT OUTER JOIN because I only want to count visits for CDs. They are in the product table, so if I use a RIGHT … Continue reading mySQL: UNION Part 2<\/span><\/a><\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[],"class_list":["post-2101","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2101","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2101"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2101\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2101"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2101"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2101"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}