Forum Discussion
Reduce data rows
I've a table of purchased items stored in the Data Warehouse. The table has almost 20 million records. The table maintains daily transactions and hence the number if rows is huge. I want to find a w...
Hey Anonymous !
You can write your SQL like below:
SELECTEXTRACT(YEAR FROM YOUR_DATE_FIELD) AS year,EXTRACT(MONTH FROM YOUR_DATE_FIELD) AS month,
CUSTOMER, GROUP, MATERIAL,SUM(CUSTOMER_QUANTITY) AS CUSTOMER_QUANTITYFROM YOUR_TABLEGROUP BYEXTRACT(YEAR FROM YOUR_DATE_FIELD),EXTRACT(MONTH FROM YOUR_DATE_FIELD),
CUSTOMER,MATERIAL,GROUPIf this helps, please mark this question as solved to help people find quickly in our community!
Thanks. Yes, I can write SQL statement as I import the data from Oracle DB.
I'm not expert with SQL, so I was wondering how would grouping discard the data and just keep the month and year?
Hey Anonymous !
You can write your SQL like below:
SELECT
EXTRACT(YEAR FROM YOUR_DATE_FIELD) AS year,
EXTRACT(MONTH FROM YOUR_DATE_FIELD) AS month,
CUSTOMER, GROUP, MATERIAL,
CUSTOMER, GROUP, MATERIAL,
SUM(CUSTOMER_QUANTITY) AS CUSTOMER_QUANTITY
FROM YOUR_TABLE
GROUP BY
EXTRACT(YEAR FROM YOUR_DATE_FIELD),
EXTRACT(MONTH FROM YOUR_DATE_FIELD),
CUSTOMER,
CUSTOMER,
MATERIAL,
GROUP
If this helps, please mark this question as solved to help people find quickly in our community!