CREATE TABLE SALES
(
PRODUCT_GROUP VARCHAR2(50),
ITEM VARCHAR2(50),
SOLD DATE,
UNITS NUMBER(9),
PRICE NUMBER(12,2)
)
Table created.
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.
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.
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.
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.
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.
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.
COMMIT
Statement processed.
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 |
---|
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 |
---|
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
COMMIT
Statement processed.
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 |
---|
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 |
---|
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 |
---|
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 expressionMore Details: https://docs.oracle.com/error-help/db/ora-00979
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 |
---|
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 |
---|
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 |
---|
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 hereMore Details: https://docs.oracle.com/error-help/db/ora-00934
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 |
---|
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 |
---|
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 |
---|