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

= 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: 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:
 * V2: -- Verbarson  talkedits 23:16, 4 March 2023 (UTC)
 * 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 talkedits 21:27, 2 March 2023 (UTC)
 * 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 talkedits 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  to get the full list of dates, then   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  talkedits 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)