Could someone help me to know how to make conditional aggregation in database reader ?
I have a raw data table (very big) that includes many records like below. I would like to just read the records that Qty> 0 and Net Sales>0, but I want to aggregate them at the same time. I wrote SQL as below, but it always aggregates all the rows , without following the conditions. Could someone help me with it? Thank you very much!
SELECT
Product,
COUNTRY,
SUM(QUANTITY) AS TOTAL_QUANTITY,
SUM(NET_SALES_LC) AS TOTAL_NET_SALES_LC
FROM
"_SYS_xxx"."xxxxxxxx/CA_COMBINED_SALES_xxx"
WHERE
CALMONTH between 201701 and 201709
AND
COUNTRY ='BE'
AND
BPRODUCT = 'A'
AND
(QUANTITY>0, and NET_SALES_LC>0)
GROUP BY
Product,
COUNTRY
The raw data table is like:
| COUNTRY | Product | CALDAY | QUANTITY | NET_SALES_LC |
| BE | A | 20170331 | 0 | -2500 |
| BE | A | 20170331 | 0 | 2500 |
| BE | B | 20170831 | 0 | 2500 |
| BE | B | 20170801 | 0 | -1123.08 |
| BE | B | 20170801 | 0 | -204.2 |
| BE | B | 20170831 | 0 | -1123.08 |
| BE | B | 20170801 | 0 | -2500 |
| BE | B | 20170831 | 0 | 1123.08 |
| BE | B | 20170901 | 0 | 204.2 |
| BE | B | 20170901 | 0 | -204.2 |
| BE | B | 20170901 | 0 | -2500 |
| BE | B | 20170929 | 0 | 4828.79 |
| BE | B | 20170901 | 0 | -1123.08 |
| BE | A | 20170929 | 0 | -1123.08 |
| BE | A | 20170929 | 0 | 2500 |
| BE | A | 20170901 | 0 | 1123.08 |
| BE | A | 20170929 | 0 | 1123.08 |
| BE | A | 20170929 | 0 | -4828.79 |
| BE | A | 20170719 | 0 | -418.61 |
| BE | A | 20170927 | 0 | -5032.99 |
| BE | A | 20170119 | 1 | 602.39 |
| BE | A | 20170130 | 7 | 4216.73 |
| BE | A | 20170125 | -7 | -4216.73 |
| BE | A | 20170116 | 6 | 3614.34 |
| BE | A | 20170217 | 2 | 1633.6 |
| BE | A | 20170307 | 2 | 1204.78 |
| BE | B | 20170417 | 1 | 602.39 |
| BE | B | 20170628 | 1 | 816.8 |
| BE | B | 20170724 | 2 | 1633.6 |
| BE | B | 20170919 | 1 | 602.39 |
| BE | B | 20170926 | 2 | 1204.78 |