Skip to main content

Return to Question

updated working query, and added sample data
Source Link
KassieB
KassieB
  • 135
  • 1
  • 9

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, asidebut not fully what I'm needing the total_orders column to return the totallooking for their distinct combinations.

SELECT DISTINCT t1.order_date, t1.p_category, CASE WHEN ( t1.issue_grp = 1 ) THEN '1' ELSE '2'  || ''2/' || '33 ' END AS issue_group, t1.srt AS srt_level, COUNT( *) AS SELECTtotal_orders  FROM  COUNT(t1database.order_date)t_con  WHERE  order_date IN FROM( '&Enter_Date_YYYYMM'  database.t_con) GROUP t2BY p_category,  WHERECASE   WHEN ( t1.order_dateissue_grp = t2.order_date  1 ) ASTHEN total_orders FROM'1'   database.t_con t1 WHERE  t1.order_dateELSE IN'2/3 (' END,  '&Enter_Date_YYYYMM'srt, )order_date ORDER BY p_category, issue_group, srt_level, order_date

The current total_orders column returning the count for each order_dateCurrent Return (so 8 columns show the same count12 rows), but I'm needing it to return the count for the distinct:

enter image description here

Needed Return (p_category, issue_group, srt_level8 rows without the tan rows being shown) combinations.:

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 going to addadding a filled_orders column, that will return a count broken down by their distinct values/calculations as wellgo before the total_orders column, but I'm just not there yet.

Any help would be greatSorry I wasn't as descriptive earlier. Thanks again!

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, aside I'm needing the total_orders column to return the total for their distinct combinations.

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

The current total_orders column returning the count for each order_date (so 8 columns show the same count), but I'm needing it to return the count for the distinct (p_category, issue_group, srt_level) combinations.

I'm eventually going to add a filled_orders column, that will return a count broken down by their distinct values/calculations as well.

Any help would be great!

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!

Source Link
KassieB
KassieB
  • 135
  • 1
  • 9

COUNT from DISTINCT values in multiple columns

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, aside I'm needing the total_orders column to return the total for their distinct combinations.

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

The current total_orders column returning the count for each order_date (so 8 columns show the same count), but I'm needing it to return the count for the distinct (p_category, issue_group, srt_level) combinations.

I'm eventually going to add a filled_orders column, that will return a count broken down by their distinct values/calculations as well.

Any help would be great!