Oracle Live SQL - Script: DHBW Datenbanken Unit 1 - 10

Statement 1

CREATE TABLE SALES
(
    PRODUCT_GROUP   VARCHAR2(50),
    ITEM            VARCHAR2(50),
    SOLD            DATE,
    UNITS           NUMBER(9),
    PRICE           NUMBER(12,2) 
)

Table created.

Statement 2

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Bag', to_date('2016-9-01', 'YYYY-MM-DD'), 1, 1.00)

1 row(s) inserted.

Statement 3

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Cable', to_date('2016-9-02', 'YYYY-MM-DD'), 4,  5.00)

1 row(s) inserted.

Statement 4

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Smartphone Case', to_date('2016-9-03', 'YYYY-MM-DD'), 20, 15.00)

1 row(s) inserted.

Statement 5

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Smartphone', to_date('2016-9-04', 'YYYY-MM-DD'), 8, 500.00)

1 row(s) inserted.

Statement 6

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Tablet', to_date('2016-9-05', 'YYYY-MM-DD'), 40, 1250.00)

1 row(s) inserted.

Statement 7

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Workstation', to_date('2016-9-06', 'YYYY-MM-DD'), 300, 2000.00)

1 row(s) inserted.

Statement 8

COMMIT

Statement processed.

Statement 9

SELECT s.*, 
       s.UNITS * s.PRICE AS "Value"     
  FROM SALES s  
 ORDER BY s.SOLD, s.PRODUCT_GROUP, s.ITEM


PRODUCT_GROUP ITEM SOLD UNITS PRICE Value
Accessories Bag 01-SEP-16 1 1 1
Accessories Cable 02-SEP-16 4 5 20
Accessories Smartphone Case 03-SEP-16 20 15 300
Electronics Smartphone 04-SEP-16 8 500 4000
Electronics Tablet 05-SEP-16 40 1250 50000
Electronics Workstation 06-SEP-16 300 2000 600000

6 rows selected.

PRODUCT_GROUP	ITEM	SOLD	UNITS	PRICE	Value
Accessories	Bag	01-SEP-16	1	1	1
Accessories	Cable	02-SEP-16	4	5	20
Accessories	Smartphone Case	03-SEP-16	20	15	300
Electronics	Smartphone	04-SEP-16	8	500	4000
Electronics	Tablet	05-SEP-16	40	1250	50000
Electronics	Workstation	06-SEP-16	300	2000	600000

Statement 10

Aggregation:

  SELECT s.PRODUCT_GROUP, 
         count(*),  
         sum(s.UNITS) AS "Sum Units",  
         sum(s.UNITS * s.PRICE) AS "Sum Value"  
    FROM SALES s 
GROUP BY s.PRODUCT_GROUP


PRODUCT_GROUP COUNT(*) Sum Units Sum Value
Accessories 3 25 321
Electronics 3 348 654000

2 rows selected.

PRODUCT_GROUP	COUNT(*)	Sum Units	Sum Value
Accessories	3	25	321
Electronics	3	348	654000

Statement 11

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Bag', to_date('2016-10-01', 'YYYY-MM-DD'), 1, 1.00)

1 row(s) inserted.

Statement 12

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Cable', to_date('2016-10-02', 'YYYY-MM-DD'), 1,  5.00)

1 row(s) inserted.

Statement 13

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Cable', to_date('2016-10-03', 'YYYY-MM-DD'), 2,  5.00)

1 row(s) inserted.

Statement 14

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Cable', to_date('2016-10-04', 'YYYY-MM-DD'), 1,  5.00)

1 row(s) inserted.

Statement 15

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Smartphone Case', to_date('2016-10-05', 'YYYY-MM-DD'), 2, 15.00)

1 row(s) inserted.

Statement 16

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Smartphone Case', to_date('2016-10-06', 'YYYY-MM-DD'), 4, 15.00)

1 row(s) inserted.

Statement 17

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Smartphone Case', to_date('2016-10-07', 'YYYY-MM-DD'), 4, 15.00)

1 row(s) inserted.

Statement 18

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Smartphone Case', to_date('2016-10-08', 'YYYY-MM-DD'), 5, 15.00)

1 row(s) inserted.

Statement 19

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Smartphone Case', to_date('2016-10-09', 'YYYY-MM-DD'), 3, 15.00)

1 row(s) inserted.

Statement 20

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Accessories', 'Smartphone Case', to_date('2016-10-10', 'YYYY-MM-DD'), 2, 15.00)

1 row(s) inserted.

Statement 21

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Smartphone', to_date('2016-10-11', 'YYYY-MM-DD'), 2, 500.00)

1 row(s) inserted.

Statement 22

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Smartphone', to_date('2016-10-12', 'YYYY-MM-DD'), 1, 500.00)

1 row(s) inserted.

Statement 23

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Smartphone', to_date('2016-10-13', 'YYYY-MM-DD'), 3, 500.00)

1 row(s) inserted.

Statement 24

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Smartphone', to_date('2016-10-14', 'YYYY-MM-DD'), 2, 500.00)

1 row(s) inserted.

Statement 25

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Tablet', to_date('2016-10-15', 'YYYY-MM-DD'), 10, 1250.00)

1 row(s) inserted.

Statement 26

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Tablet', to_date('2016-10-16', 'YYYY-MM-DD'), 15, 1250.00)

1 row(s) inserted.

Statement 27

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Tablet', to_date('2016-10-17', 'YYYY-MM-DD'), 5, 1250.00)

1 row(s) inserted.

Statement 28

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Tablet', to_date('2016-10-18', 'YYYY-MM-DD'), 10, 1250.00)

1 row(s) inserted.

Statement 29

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Workstation', to_date('2016-10-19', 'YYYY-MM-DD'), 10, 2000.00)

1 row(s) inserted.

Statement 30

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Workstation', to_date('2016-10-20', 'YYYY-MM-DD'), 90, 2000.00)

1 row(s) inserted.

Statement 31

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Workstation', to_date('2016-10-21', 'YYYY-MM-DD'), 25, 2000.00)

1 row(s) inserted.

Statement 32

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Workstation', to_date('2016-10-22', 'YYYY-MM-DD'), 50, 2000.00)

1 row(s) inserted.

Statement 33

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Workstation', to_date('2016-10-23', 'YYYY-MM-DD'), 15, 2000.00)

1 row(s) inserted.

Statement 34

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Workstation', to_date('2016-10-24', 'YYYY-MM-DD'), 10, 2000.00)

1 row(s) inserted.

Statement 35

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Workstation', to_date('2016-10-25', 'YYYY-MM-DD'), 9, 2000.00)

1 row(s) inserted.

Statement 36

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Workstation', to_date('2016-10-26', 'YYYY-MM-DD'), 1, 2000.00)

1 row(s) inserted.

Statement 37

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Workstation', to_date('2016-10-27', 'YYYY-MM-DD'), 70, 2000.00)

1 row(s) inserted.

Statement 38

INSERT INTO SALES (PRODUCT_GROUP, ITEM, SOLD, UNITS, PRICE) 
           VALUES ('Electronics', 'Workstation', to_date('2016-10-28', 'YYYY-MM-DD'), 20, 2000.00)

1 row(s) inserted.

Statement 39

COMMIT

Statement processed.

Statement 40

SELECT *  
  FROM SALES s 
 ORDER BY s.SOLD, s.PRODUCT_GROUP, s.ITEM


PRODUCT_GROUP ITEM SOLD UNITS PRICE
Accessories Bag 01-SEP-16 1 1
Accessories Cable 02-SEP-16 4 5
Accessories Smartphone Case 03-SEP-16 20 15
Electronics Smartphone 04-SEP-16 8 500
Electronics Tablet 05-SEP-16 40 1250
Electronics Workstation 06-SEP-16 300 2000
Accessories Bag 01-OCT-16 1 1
Accessories Cable 02-OCT-16 1 5
Accessories Cable 03-OCT-16 2 5
Accessories Cable 04-OCT-16 1 5
Accessories Smartphone Case 05-OCT-16 2 15
Accessories Smartphone Case 06-OCT-16 4 15
Accessories Smartphone Case 07-OCT-16 4 15
Accessories Smartphone Case 08-OCT-16 5 15
Accessories Smartphone Case 09-OCT-16 3 15
Accessories Smartphone Case 10-OCT-16 2 15
Electronics Smartphone 11-OCT-16 2 500
Electronics Smartphone 12-OCT-16 1 500
Electronics Smartphone 13-OCT-16 3 500
Electronics Smartphone 14-OCT-16 2 500
Electronics Tablet 15-OCT-16 10 1250
Electronics Tablet 16-OCT-16 15 1250
Electronics Tablet 17-OCT-16 5 1250
Electronics Tablet 18-OCT-16 10 1250
Electronics Workstation 19-OCT-16 10 2000
Electronics Workstation 20-OCT-16 90 2000
Electronics Workstation 21-OCT-16 25 2000
Electronics Workstation 22-OCT-16 50 2000
Electronics Workstation 23-OCT-16 15 2000
Electronics Workstation 24-OCT-16 10 2000
Electronics Workstation 25-OCT-16 9 2000
Electronics Workstation 26-OCT-16 1 2000
Electronics Workstation 27-OCT-16 70 2000
Electronics Workstation 28-OCT-16 20 2000

34 rows selected.

PRODUCT_GROUP	ITEM	SOLD	UNITS	PRICE
Accessories	Bag	01-SEP-16	1	1
Accessories	Cable	02-SEP-16	4	5
Accessories	Smartphone Case	03-SEP-16	20	15
Electronics	Smartphone	04-SEP-16	8	500
Electronics	Tablet	05-SEP-16	40	1250
Electronics	Workstation	06-SEP-16	300	2000
Accessories	Bag	01-OCT-16	1	1
Accessories	Cable	02-OCT-16	1	5
Accessories	Cable	03-OCT-16	2	5
Accessories	Cable	04-OCT-16	1	5
Accessories	Smartphone Case	05-OCT-16	2	15
Accessories	Smartphone Case	06-OCT-16	4	15
Accessories	Smartphone Case	07-OCT-16	4	15
Accessories	Smartphone Case	08-OCT-16	5	15
Accessories	Smartphone Case	09-OCT-16	3	15
Accessories	Smartphone Case	10-OCT-16	2	15
Electronics	Smartphone	11-OCT-16	2	500
Electronics	Smartphone	12-OCT-16	1	500
Electronics	Smartphone	13-OCT-16	3	500
Electronics	Smartphone	14-OCT-16	2	500
Electronics	Tablet	15-OCT-16	10	1250
Electronics	Tablet	16-OCT-16	15	1250
Electronics	Tablet	17-OCT-16	5	1250
Electronics	Tablet	18-OCT-16	10	1250
Electronics	Workstation	19-OCT-16	10	2000
Electronics	Workstation	20-OCT-16	90	2000
Electronics	Workstation	21-OCT-16	25	2000
Electronics	Workstation	22-OCT-16	50	2000
Electronics	Workstation	23-OCT-16	15	2000
Electronics	Workstation	24-OCT-16	10	2000
Electronics	Workstation	25-OCT-16	9	2000
Electronics	Workstation	26-OCT-16	1	2000
Electronics	Workstation	27-OCT-16	70	2000
Electronics	Workstation	28-OCT-16	20	2000

Statement 41

Group By:

  SELECT s.PRODUCT_GROUP,  
         s.ITEM, 
         count(*),  
         sum(s.UNITS) AS "Sum Units",  
         sum(s.UNITS * s.PRICE) AS "Sum Value"  
    FROM SALES s 
GROUP BY s.PRODUCT_GROUP, s.ITEM 
ORDER BY s.PRODUCT_GROUP, s.ITEM


PRODUCT_GROUP ITEM COUNT(*) Sum Units Sum Value
Accessories Bag 2 2 2
Accessories Cable 4 8 40
Accessories Smartphone Case 7 40 600
Electronics Smartphone 5 16 8000
Electronics Tablet 5 80 100000
Electronics Workstation 11 600 1200000

6 rows selected.

PRODUCT_GROUP	ITEM	COUNT(*)	Sum Units	Sum Value
Accessories	Bag	2	2	2
Accessories	Cable	4	8	40
Accessories	Smartphone Case	7	40	600
Electronics	Smartphone	5	16	8000
Electronics	Tablet	5	80	100000
Electronics	Workstation	11	600	1200000

Statement 42

Gruppiert nach Monat:

  SELECT trunc(SOLD, 'MM'), 
         to_char(trunc(SOLD, 'MM'), 'YYYY') AS JAHR,  
         to_char(trunc(SOLD, 'MM'), 'MM') AS MONAT,   
         count(*),   
         sum(s.UNITS) AS "Sum Units",   
         sum(s.UNITS * s.PRICE) AS "Sum Value"   
    FROM SALES s  
GROUP BY trunc(SOLD, 'MM')   
ORDER BY trunc(SOLD, 'MM')


TRUNC(SOLD,'MM') JAHR MONAT COUNT(*) Sum Units Sum Value
01-SEP-16 2016 09 6 373 654321
01-OCT-16 2016 10 28 373 654321

2 rows selected.

TRUNC(SOLD,'MM')	JAHR	MONAT	COUNT(*)	Sum Units	Sum Value
01-SEP-16	2016	09	6	373	654321
01-OCT-16	2016	10	28	373	654321

Statement 43

Nicht möglich!!!

Columns ausgeben, nach denen nicht gruppiert wurde?

  SELECT s.PRODUCT_GROUP,   
         s.ITEM,  
         count(*),   
         sum(s.UNITS) AS "Sum Units",   
         sum(s.UNITS * s.PRICE) AS "Sum Value"   
    FROM SALES s  
GROUP BY s.PRODUCT_GROUP  
ORDER BY s.PRODUCT_GROUP

ORA-00979: not a GROUP BY expression

More Details: https://docs.oracle.com/error-help/db/ora-00979

Statement 44

  SELECT to_char(trunc(SOLD, 'MM'), 'YYYY-MM') AS MONAT,  
         s.PRODUCT_GROUP,  
         count(*),  
         sum(s.UNITS) AS "Sum Units",  
         sum(s.UNITS * s.PRICE) AS "Sum Value"  
    FROM SALES s 
GROUP BY trunc(SOLD, 'MM'), s.PRODUCT_GROUP  
ORDER BY trunc(SOLD, 'MM'), s.PRODUCT_GROUP


MONAT PRODUCT_GROUP COUNT(*) Sum Units Sum Value
2016-09 Accessories 3 25 321
2016-09 Electronics 3 348 654000
2016-10 Accessories 10 25 321
2016-10 Electronics 18 348 654000

4 rows selected.

MONAT	PRODUCT_GROUP	COUNT(*)	Sum Units	Sum Value
2016-09	Accessories	3	25	321
2016-09	Electronics	3	348	654000
2016-10	Accessories	10	25	321
2016-10	Electronics	18	348	654000

Statement 45

  SELECT s.PRODUCT_GROUP, 
         s.ITEM,
         trunc(SOLD, 'MM') AS MONAT,
         count(*), 
         sum(s.UNITS) AS "Sum Units", 
         sum(s.UNITS * s.PRICE) AS "Sum Value" 
    FROM SALES s
GROUP BY s.PRODUCT_GROUP, s.ITEM, trunc(SOLD, 'MM')  
ORDER BY s.PRODUCT_GROUP, s.ITEM, trunc(SOLD, 'MM')


PRODUCT_GROUP ITEM MONAT COUNT(*) Sum Units Sum Value
Accessories Bag 01-SEP-16 1 1 1
Accessories Bag 01-OCT-16 1 1 1
Accessories Cable 01-SEP-16 1 4 20
Accessories Cable 01-OCT-16 3 4 20
Accessories Smartphone Case 01-SEP-16 1 20 300
Accessories Smartphone Case 01-OCT-16 6 20 300
Electronics Smartphone 01-SEP-16 1 8 4000
Electronics Smartphone 01-OCT-16 4 8 4000
Electronics Tablet 01-SEP-16 1 40 50000
Electronics Tablet 01-OCT-16 4 40 50000
Electronics Workstation 01-SEP-16 1 300 600000
Electronics Workstation 01-OCT-16 10 300 600000

12 rows selected.

PRODUCT_GROUP	ITEM	MONAT	COUNT(*)	Sum Units	Sum Value
Accessories	Bag	01-SEP-16	1	1	1
Accessories	Bag	01-OCT-16	1	1	1
Accessories	Cable	01-SEP-16	1	4	20
Accessories	Cable	01-OCT-16	3	4	20
Accessories	Smartphone Case	01-SEP-16	1	20	300
Accessories	Smartphone Case	01-OCT-16	6	20	300
Electronics	Smartphone	01-SEP-16	1	8	4000
Electronics	Smartphone	01-OCT-16	4	8	4000
Electronics	Tablet	01-SEP-16	1	40	50000
Electronics	Tablet	01-OCT-16	4	40	50000
Electronics	Workstation	01-SEP-16	1	300	600000
Electronics	Workstation	01-OCT-16	10	300	600000

Statement 46

  SELECT trunc(SOLD, 'MM') AS MONAT,
         count(*), 
         sum(s.UNITS) AS "Sum Units", 
         sum(s.UNITS * s.PRICE) AS "Sum Value" 
    FROM SALES s
   WHERE SOLD >= to_date('2016-10-01', 'YYYY-MM-DD')
GROUP BY trunc(SOLD, 'MM') 
ORDER BY trunc(SOLD, 'MM')


MONAT COUNT(*) Sum Units Sum Value
01-OCT-16 28 373 654321

MONAT	COUNT(*)	Sum Units	Sum Value
01-OCT-16	28	373	654321

Statement 47

Nicht möglich!!!

Nach Aggregationen filtern in der Where-Clause?

  SELECT s.PRODUCT_GROUP,    
         count(*),    
         sum(s.UNITS) AS "Sum Units",    
         sum(s.UNITS * s.PRICE) AS "Sum Value"    
    FROM SALES s   
   WHERE sum(s.UNITS * s.PRICE) > 1000   
GROUP BY s.PRODUCT_GROUP     
ORDER BY s.PRODUCT_GROUP

ORA-00934: group function is not allowed here

More Details: https://docs.oracle.com/error-help/db/ora-00934

Statement 48

Nach Aggregationen filtern in der Having-Clause!

  SELECT s.PRODUCT_GROUP,  
         s.ITEM, 
         trunc(SOLD, 'MM') AS MONAT, 
         count(*),  
         sum(s.UNITS) AS "Sum Units",  
         sum(s.UNITS * s.PRICE) AS "Sum Value"  
    FROM SALES s 
GROUP BY s.PRODUCT_GROUP, s.ITEM, trunc(SOLD, 'MM')   
  HAVING sum(s.UNITS * s.PRICE) > 1000 
ORDER BY s.PRODUCT_GROUP, s.ITEM, trunc(SOLD, 'MM')


PRODUCT_GROUP ITEM MONAT COUNT(*) Sum Units Sum Value
Electronics Smartphone 01-SEP-16 1 8 4000
Electronics Smartphone 01-OCT-16 4 8 4000
Electronics Tablet 01-SEP-16 1 40 50000
Electronics Tablet 01-OCT-16 4 40 50000
Electronics Workstation 01-SEP-16 1 300 600000
Electronics Workstation 01-OCT-16 10 300 600000

6 rows selected.

PRODUCT_GROUP	ITEM	MONAT	COUNT(*)	Sum Units	Sum Value
Electronics	Smartphone	01-SEP-16	1	8	4000
Electronics	Smartphone	01-OCT-16	4	8	4000
Electronics	Tablet	01-SEP-16	1	40	50000
Electronics	Tablet	01-OCT-16	4	40	50000
Electronics	Workstation	01-SEP-16	1	300	600000
Electronics	Workstation	01-OCT-16	10	300	600000

Statement 49

WHERE-Clause und HAVING-Clause:

  SELECT s.PRODUCT_GROUP,   
         s.ITEM,  
         trunc(SOLD, 'MM') AS MONAT,  
         count(*),   
         sum(s.UNITS) AS "Sum Units",   
         sum(s.UNITS * s.PRICE) AS "Sum Value"   
    FROM SALES s  
   WHERE s.ITEM != 'Smartphone'     
GROUP BY s.PRODUCT_GROUP, s.ITEM, trunc(SOLD, 'MM')    
  HAVING sum(s.UNITS * s.PRICE) > 1000  
ORDER BY s.PRODUCT_GROUP, s.ITEM, trunc(SOLD, 'MM')


PRODUCT_GROUP ITEM MONAT COUNT(*) Sum Units Sum Value
Electronics Tablet 01-SEP-16 1 40 50000
Electronics Tablet 01-OCT-16 4 40 50000
Electronics Workstation 01-SEP-16 1 300 600000
Electronics Workstation 01-OCT-16 10 300 600000

4 rows selected.

PRODUCT_GROUP	ITEM	MONAT	COUNT(*)	Sum Units	Sum Value
Electronics	Tablet	01-SEP-16	1	40	50000
Electronics	Tablet	01-OCT-16	4	40	50000
Electronics	Workstation	01-SEP-16	1	300	600000
Electronics	Workstation	01-OCT-16	10	300	600000

Statement 50

HAVING Clause: "ab 20000 Euro Umsatz" "mehr als 30 Stück verkauft" "mehr als 5 Verkäufe (Datensätze)"

HAVING Clause mit mehreren Alternativen

  SELECT s.PRODUCT_GROUP,   
         s.ITEM,  
         trunc(SOLD, 'MM') AS MONAT,  
         count(*),   
         sum(s.UNITS) AS "Sum Units",   
         max(s.UNITS * s.PRICE) AS "Sum Value"   
    FROM SALES s 
GROUP BY s.PRODUCT_GROUP, s.ITEM, trunc(SOLD, 'MM')    
  HAVING sum(s.UNITS * s.PRICE) >= 200000 
      OR sum(s.UNITS) > 30 
      OR count(*) > 5 
ORDER BY s.PRODUCT_GROUP, s.ITEM, trunc(SOLD, 'MM')


PRODUCT_GROUP ITEM MONAT COUNT(*) Sum Units Sum Value
Accessories Smartphone Case 01-OCT-16 6 20 75
Electronics Tablet 01-SEP-16 1 40 50000
Electronics Tablet 01-OCT-16 4 40 18750
Electronics Workstation 01-SEP-16 1 300 600000
Electronics Workstation 01-OCT-16 10 300 180000

5 rows selected.

DHBW Datenbanken Unit 1 - 10 - GROUP BY und HAVING

Additional Information