Nsryjdtyk

I have tried creating a case statement but doesn't seem to give me what i want. Id like to get a split of the table (which is at a product level) and aggregate at an order level of items which contain quantity of 1.

Any ideas on how I would do this?

order id | Product | Quantity 

---------+---------+--------------

 11111   | sdsd4   | 1 (single item )

 22222   | sasas   | 1 (multiple items)

 22222   | wertt   | 1 (multiple items)

I'd like to get a case statement to add another column to split out orders with quantity = 1 and orders greater 1

Any idea on how I would do this?

The desired outcome would be the column in (brackets)

I could then count the orders and bring in the newly created column as the dimension

More detail here:

enter image description here

Attached is an image of table structure.

Logic, if quantity = 1 and 1 order then single item order
if order has one item but multiples of same item non single item order
if order has more than one product then non single item order

Below is for BigQuery Standard SQL

#standardSQL

SELECT *, 

  CASE COUNT(DISTINCT Product) OVER(PARTITION BY order_id) 

    WHEN 1 THEN 'Single Item Order'

    ELSE 'Multipple Items Order'

  END Single_or_Multiple

FROM `project.dataset.table`

You can test, play with above using dummy data as below

#standardSQL

WITH `project.dataset.table` AS (

  SELECT 11111 order_id, 'sdsd4' Product, 1 Quantity UNION ALL

  SELECT 22222, 'sasas', 2 UNION ALL

  SELECT 22222, 'wertt', 1

)

SELECT *, 

  CASE COUNT(DISTINCT Product) OVER(PARTITION BY order_id) 

    WHEN 1 THEN 'Single Item Order'

    ELSE 'Multipple Items Order'

  END Single_or_Multiple

FROM `project.dataset.table`   

with result

Row order_id    Product Quantity    Single_or_Multiple   

1   11111       sdsd4   1           Single Item Order    

2   22222       sasas   2           Multipple Items Order    

3   22222       wertt   1           Multipple Items Order    

If your database supports analytic functions, then you can use a query like this one:

SELECT *,

      CASE WHEN count("Product") OVER (partition by "order id") > 1

      THEN 'multiple items' ELSE 'single item'

      END As "How many items"

FROM Table1

Demo: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=b659279fc16d2084cb1cf4a3bea361a1

If I understand this right, you could use a subquery to get the count of records for an order and flag a record, if this count is larger then 1 and the quantity is equal to 1.

SELECT t1.order_id,

       t1.product,

       t1.quantity,

       CASE

         WHEN t1.quantity = 1

              AND (SELECT count(*)

                          FROM elbat t2

                          WHERE t2.order_id = t1.order_id) > 1 THEN

           'flag'

         ELSE

           'no flag'

       END flag

       FROM elbat t1;