Wikipedia:Reference desk/Archives/Computing/2023 March 2

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

March 2

SQL question

I'm using Oracle SQL. I've got a situation where I've got a table with a couple of fields on it that contain dates, call them DATE1 and DATE2; every row will have a DATE1, but only some rows will have a DATE2. I want to be able to count the number of rows by year and month, so that at the end I can say that for year 2023 month 1 there were 245 items where DATE1 met those two criteria and 106 items where DATE2 met those criteria. Simply grouping by the extracted months/years from the date fields and counting the rows doesn't work because I'll skip over year/month combination where no date fields match the criteria (I'd want to see a zero or even a null) and also because it will only group to the level that all four conditions are met and I want it to group to a defined list of years and months.

I thought I could make use of DUAL to do that, but if so, I can't figure it out. Like, if I could somehow specify years (2021, 2022, 2023) and months (1,2,3,4,5,6,7,8,9,10,11,12) in DUAL I could then do a cross join to the main data table and group by the years and months I defined in DUAL. But either DUAL doesn't work that way or I'm not getting the syntax correct. For example, this: SELECT YEAR_NO, MONTH_NO FROM DUAL WHERE YEAR_NO in (2021, 2022, 2023) AND MONTH_NO in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) just errors out. Am I even on the right track? Any suggestions? Matt Deres (talk) 16:28, 2 March 2023 (UTC)

You need to create

• a list of different DATE1 values, with counts for each entry
• a list of different non-null DATE2 values, with counts for each entry
• a combination of every value from both of those lists, with the counts from each list (0 if that date does not appear)

In SQL Server, I would use

• V1: SELECT DATE1, COUNT(*) AS count1 FROM table GROUP BY DATE1
• V2: SELECT DATE2, COUNT(*) AS count2 FROM table WHERE DATE2 IS NOT NULL GROUP BY DATE2 --edit: final DATE1 corrected to DATE2-- Verbarson  ^talk_edits 23:16, 4 March 2023 (UTC)
• SELECT ISNULL(DATE1, DATE2) AS DATEALL, ISNULL(count1, 0) AS count1, ISNULL(count2, 0) AS count2 FROM V1 FULL OUTER JOIN V2 ON V1.DATE1 = V2.DATE2

V1 and V2 could be created as separate views, though I might use them as Common Table Expressions within a single view including all three SELECT statements. I'm not familiar with Oracle SQL's foibles in this area. -- Verbarson  ^talk_edits 21:27, 2 March 2023 (UTC)

I could create those two things as subqueries, I think, and that would go a long way to getting me what I need, but I'd still be missing dates. Let's say that neither date1 nor date2 happens to have any entries for March 2022; I'd still want March 2022 to be there, showing zero or null. We're tracking activities, and having nothing happen is as important as having something happen. That's why I was thinking I needed to create something in dual that provided all the possible options (x years, 12 months). Matt Deres (talk) 14:02, 8 March 2023 (UTC)

Sorry, I had not realised that you needed all dates. Try SELECT YEAR_NO FROM DUAL WHERE YEAR_NO in (2021, 2022, 2023) CROSS JOIN SELECT MONTH_NO FROM DUAL WHERE MONTH_NO in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) to get the full list of dates, then LEFT OUTER JOIN to each of the subqueries by YEAR_NO and MONTH_NO. My suggested queries would have to be modified to contain year and month columns. -- Verbarson  ^talk_edits 15:27, 8 March 2023 (UTC)

Okay, so I had the right idea with dual, but I was doing it incorrectly by trying to do both sides at the same time (code in my OP). Thanks! Matt Deres (talk) 16:04, 8 March 2023 (UTC)