COUNT from DISTINCT values in multiple columns

Question

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):

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

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!

I think this answer has what you're looking for: https://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct. — Jawi, Dec 11 '18 at 16:22
Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) — Juan Carlos Oropeza, Dec 11 '18 at 16:22
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. — xQbert, Dec 11 '18 at 16:43

score 1 · Answer 1 · answered Dec 11 '18 at 16:31

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.

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. — KassieB, Dec 11 '18 at 16:46
OK, then ignore the bit about `to_date()` I suppose... And as commented above, sample data etc. woudl make things much clearer. — Alex Poole, Dec 11 '18 at 17:06
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! — KassieB, Dec 11 '18 at 17:40

score 0 · Answer 2 · answered Dec 12 '18 at 20:50

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

COUNT from DISTINCT values in multiple columns

2 Answers2