Counting number of cases within a case

Question

I'm looking to count the number of people who spent a certain amount at a certain store in a certain year.

I tried making a nested CASE expression, the first that looks at transactions at a certain year, the second looking at the sum of a person's transaction to a certain store, but Oracle didn't like nested CASE expressions (unless I was doing it wrong).

From this SQL example:

CREATE TABLE table_name ( PersonId, StoreId, AmountSpent, Year) as
  select 1, 1, 60, 2017 from dual union
  select 1, 1, 50, 2017  from dual union
  select 1, 2, 70, 2018 from dual union
  select 2, 1, 10, 2017 from dual union
  select 2, 1, 10, 2017  from dual union
  select 2, 1, 200, 2018  from dual union
  select 2, 2, 60, 2018 from dual union
  select 2, 2, 60, 2018  from dual union
  select 3, 1, 25, 2017 from dual union
  select 3, 2, 200, 2017 from dual union
  select 3, 2, 200, 2018  from dual;

Select
  StoreId,
  SUM(CASE WHEN Year = '2017'
        THEN AmountSpent
        ELSE 0
      End) Year17,
   SUM(CASE WHEN Year = '2018'
        THEN AmountSpent
        ELSE 0
      End) Year18
FROM table_name
GROUP BY StoreId;

   STOREID     YEAR17     YEAR18
---------- ---------- ----------
         1        145        200
         2        200        330

Would someone be able to make an output like this? I think some numbers might be wrong, but it seems like most of the people get the gist of where I'm going.

+----+---------+------------+---------------------+-------------------+---------------------+---------------------+------------+-------------------+---------------------+
|    | STOREID | Y17_INCOME | Y17_SPENT_BELOW_100 | Y17_SPENT_100_150 | Y17_SPENT_ABOVE_150 | Y18_INCOME | Y18_SPENT_BELOW_100 | Y18_SPENT_100_150 | Y18_SPENT_ABOVE_150 |
+----+---------+------------+---------------------+-------------------+---------------------+------------+---------------------+-------------------+---------------------+
| 1  | 1       | 145        | 2                   | 1                 | 0                   | 200        | 2                   | 0                 | 1                   |
+----+---------+------------+---------------------+-------------------+---------------------+---------------------+------------+-------------------+---------------------+
| 2  | 2       | 200        | 0                   | 0                 | 1                   | 330        | 0                   | 0                 | 1                   |
+----+---------+------------+---------------------+-------------------+---------------------+------------+---------------------+-------------------+---------------------+

Not sure if this is possible, but it would be great if so!

Answer 1

You can do store/person totals in an inline view, which on its own produces:

SELECT
  StoreId,
  PersonId,
  Year,
  SUM(AmountSpent) AS TotalSpent
FROM table_name
GROUP BY
  StoreId,
  PersonId,
  Year
ORDER BY
  StoreId,
  Year,
  PersonId;

   STOREID   PERSONID       YEAR TOTALSPENT
---------- ---------- ---------- ----------
         1          1       2017        110
         1          2       2017         10
         1          3       2017         25
         1          2       2018        200
         2          3       2017        200
         2          1       2018         70
         2          2       2018         60
         2          3       2018        200

and then use multiple separate case expressions and aggregates in an outer query:

SELECT
  StoreId,
  SUM(CASE WHEN Year = '2017'
        THEN TotalSpent
        ELSE 0
      END) Year17,
  COUNT(CASE WHEN Year = '2017'
        AND TotalSpent < 100
        THEN TotalSpent
      END) AS Year17lt100,
  COUNT(CASE WHEN Year = '2017'
        AND TotalSpent >= 100
        AND TotalSpent < 150
        THEN TotalSpent
      END) AS Year17gte100lt150,
  COUNT(CASE WHEN Year = '2017'
        AND TotalSpent >= 150
        THEN TotalSpent
      END) AS Year17gte150,
   SUM(CASE WHEN Year = '2018'
        THEN TotalSpent
        ELSE 0
      END) Year18,
  COUNT(CASE WHEN Year = '2018'
        AND TotalSpent < 100
        THEN TotalSpent
      END) AS Year18lt100,
  COUNT(CASE WHEN Year = '2018'
        AND TotalSpent >= 100
        AND TotalSpent < 150
        THEN TotalSpent
      END) AS Year18gte100lt150,
  COUNT(CASE WHEN Year = '2017'
        AND TotalSpent >= 150
        THEN TotalSpent
      END) AS Year18gte150
FROM (
  SELECT
    StoreId,
    PersonId,
    Year,
    SUM(AmountSpent) AS TotalSpent
  FROM table_name
  GROUP BY
    StoreId,
    PersonId,
    Year
)
GROUP BY StoreId
ORDER BY StoreId;

which gets:

   STOREID     YEAR17 YEAR17LT100 YEAR17GTE100LT150 YEAR17GTE150     YEAR18 YEAR18LT100 YEAR18GTE100LT150 YEAR18GTE150
---------- ---------- ----------- ----------------- ------------ ---------- ----------- ----------------- ------------
         1        145           2                 1            0        200           0                 0            0
         2        200           0                 0            1        330           2                 0            1

That doesn't match the output in your image, but I think that's wrong...

I've also changed the buckets slightly from what your column headings suggested, but you can tweak those if you do really want what you showed.

You could also reduce the code and repetition by using a pivot() clause, if you're on a version of Oracle which supports that; which can provide multiple outputs and allows conditional aggregation too:

SELECT *
FROM (
  SELECT
    StoreId,
    Year,
    SUM(AmountSpent) AS TotalSpent
  FROM table_name
  GROUP BY
    StoreId,
    PersonId,
    Year
)
PIVOT (
  SUM(TotalSpent) as total,
  COUNT(CASE WHEN TotalSpent < 100 THEN TotalSpent END) as lt100,
  COUNT(CASE WHEN TotalSpent >= 100 AND TotalSpent < 150 THEN TotalSpent END) as gte100lt150,
  COUNT(CASE WHEN TotalSpent >= 150 THEN TotalSpent END) as gte150
  FOR Year IN (2017 as year17, 2018 as year18)
)
ORDER BY StoreId;

   STOREID YEAR17_TOTAL YEAR17_LT100 YEAR17_GTE100LT150 YEAR17_GTE150 YEAR18_TOTAL YEAR18_LT100 YEAR18_GTE100LT150 YEAR18_GTE150
---------- ------------ ------------ ------------------ ------------- ------------ ------------ ------------------ -------------
         1          145            2                  1             0          200            0                  0             1
         2          200            0                  0             1          330            2                  0             1

Oracle will expand that under the hood to look like the original longer query above, but it's easier to maintain.

Updated SQL Fiddle.

Answer 2

I don't think your data matches your expected output.

You need a subquery where you sum spendings of each person yearly for each store ( grouping by store & person). Then you will need to group only by store and use sum to retrieve income for given year. count + case + distinct is the key to count unique person ids that have spent money and assign them to amount spending group (one of three columns for each year).

Notice that yearXX_spending column already holds sum of amount spent by a person for a given store and year (and this is coming from subquery):

select
    storeid
  , sum(year17_spending) as y17_income
  , count(distinct case when year17_spending < 100 then personid end) as y17_spent_below_100
  , count(distinct case when year17_spending between 100 and 150 then personid end) as y17_spent_100_150
  , count(distinct case when year17_spending > 150 then personid end) as y17_spent_above_150
  , sum(year18_spending) as y18_income
  , count(distinct case when year18_spending < 100 then personid end) as y18_spent_below_100
  , count(distinct case when year18_spending between 100 and 150 then personid end) as y18_spent_100_150
  , count(distinct case when year18_spending > 150 then personid end) as y18_spent_above_150
from (
  select 
      storeid
    , personid
    , sum(case when year = 2017 then amountspent end) as year17_spending
    , sum(case when year = 2018 then amountspent end) as year18_spending
  from table_name
  group by storeid, personid
  ) t
group by storeid

Output for your sample data:

+----+---------+------------+---------------------+-------------------+---------------------+---------------------+------------+-------------------+---------------------+
|    | STOREID | Y17_INCOME | Y17_SPENT_BELOW_100 | Y17_SPENT_100_150 | Y17_SPENT_ABOVE_150 | Y18_INCOME | Y18_SPENT_BELOW_100 | Y18_SPENT_100_150 | Y18_SPENT_ABOVE_150 |
+----+---------+------------+---------------------+-------------------+---------------------+------------+---------------------+-------------------+---------------------+
| 1  | 1       | 145        | 2                   | 1                 | 0                   | 200        | 2                   | 0                 | 1                   |
+----+---------+------------+---------------------+-------------------+---------------------+---------------------+------------+-------------------+---------------------+
| 2  | 2       | 200        | 0                   | 0                 | 1                   | 330        | 0                   | 0                 | 1                   |
+----+---------+------------+---------------------+-------------------+---------------------+------------+---------------------+-------------------+---------------------+

Also note that by doing UNION when you are building sample data, you're getting rid of duplicates so that line:

select 2, 1, 10, 2017

goes to the table only once (not twice). If you mean to include everything without removing duplicates, use UNION ALL instead.