0

If this has been asked before, I apologize, I wasn't able to find a question/solution like it before breaking down and posting. I have the below query (using Oracle SQL) that works fine in a sense, but not fully what I'm looking for. 

SELECT order_date, p_category, CASE WHEN ( issue_grp = 1 ) THEN '1' ELSE '2/3 ' END AS issue_group, srt AS srt_level, COUNT(*) AS total_orders FROM database.t_con WHERE order_date IN ( '&Enter_Date_YYYYMM' ) GROUP BY p_category, CASE WHEN ( issue_grp = 1 ) THEN '1' ELSE '2/3 ' END, srt, order_date ORDER BY p_category, issue_group, srt_level, order_date

Current Return (12 rows):

enter image description here

Needed Return (8 rows without the tan rows being shown):

enter image description here

Here is the logic of total_order column that I'm expecting:

  • count of order_date where (srt_level = 80 + 100 + Late) ... 'Late' counts needed to be added to the total, just not be displayed

I'm eventually adding a filled_orders column that will go before the total_orders column, but I'm just not there yet.

Sorry I wasn't as descriptive earlier. Thanks again!

Improve this question
4
  • 1
    I think this answer has what you're looking for: stackoverflow.com/questions/1521605/…. Commented Dec 11, 2018 at 16:22
  • 2
    Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example Commented Dec 11, 2018 at 16:22
  • 2
    Analytical functions could help you here as well. count(*) over (Partition by p_category, issue_group, srt_level)? lets you count independent of the grouping within the query. But I do not fully understand your question/query so this may be off point. Sample data and expected results would go along way to helping. Commented Dec 11, 2018 at 16:43
  • Updated ... sorry! Commented Dec 11, 2018 at 18:40

2 Answers 2

Reset to default
1

You don't appear to need a subquery; if you want the count for each combination of values then group by those, and aggregate at that level; something like:

SELECT t1.order_date, t1.p_category, CASE WHEN ( t1.issue_grp = 1 ) THEN '1' ELSE '2/3 ' END AS issue_group, t1.srt AS srt_level, COUNT(*) AS total_orders FROM database.t_con t1 WHERE t1.order_date = TO_DATE ( '&Enter_Date_YYYYMM', 'YYYYMM' ) GROUP BY t1.p_category, CASE WHEN ( t1.issue_grp = 1 ) THEN '1' ELSE '2/3 ' END, t1.srt, t1.order_date ORDER BY p_category, issue_group, srt_level, order_date;

You shouldn't be relying on implicit conversion and NLS settings for your date argument (assuming order_date is actually a date column, not a string), so I've used an explicit TO_DATE() call, using the format suggested by your substitution variable name and prompt.

However, that will give you the first day of the supplied month, since a day number isn't being supplied. It's more likely that you either want to prompt for a full date, or (possibly) just the year/month but want to include all days in that month - which IN() will not do, if that was your intention. It also implies that stored dates all have their time portions set to midnight, as that is all it will match on. If those values have non-midnight times then you need a range to pick those up too.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Updated my query and it's currently running... Will update when it's finished. Unfortunately the order_date column is stored as a varchar2 instead of date. I asked why this was, and wasn't given a clear answer yet.
OK, then ignore the bit about to_date() I suppose... And as commented above, sample data etc. woudl make things much clearer.
Your updates worked to an extent, I'll explain more above. I had to wait for the query to finish running before I could get the sample data. I'm going to be updating momentarily with additions. Thanks!
0

I got it working to the extent of what my question was. Just needed to nest each column where counts/calculations were happening. 

SELECT order_date, p_category, issue_group, srt_level, order_count, SUM(order_count) OVER( PARTITION BY order_date, issue_group, p_category ) AS total_orders FROM ( SELECT order_date, p_category, CASE WHEN ( issue_grp = 1 ) THEN '1' ELSE '2/3 ' END AS issue_group, srt AS srt_level, COUNT(*) AS order_count FROM database.t_con WHERE order_date IN ( '&Enter_Date_YYYYMM' ) GROUP BY p_category, CASE WHEN ( issue_grp = 1 ) THEN '1' ELSE '2/3 ' END, srt, order_date ) ORDER BY order_date, p_category, issue_group
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.