This statement works fine and returns only the groups which total sum is >= than 20000:
Select THIS_OFFICE_ID as OFID_PCC_NR, PUSHSTAT_DATE AS THE_DATE,
SUM(TRANSACTION_CNT) as Total_Count, REQUIRED.REQ_TOTAL as OFID_PCC_Total FROM CAM_PUSH_STATISTICSTB
INNER JOIN
(SELECT THIS_OFFICE_ID, SUM(CAST(TRANSACTION_CNT as BIGINT)) FROM CAM_PUSH_STATISTICSTB
WHERE pushstat_date BETWEEN '3/3/2017' AND '3/7/2017'
GROUP BY THIS_OFFICE_ID) AS REQUIRED(REQ_OFFICE_ID, REQ_TOTAL) ON THIS_OFFICE_ID=REQUIRED.REQ_OFFICE_ID
WHERE REQUIRED.REQ_TOTAL > 20000
AND pushstat_date BETWEEN '3/3/2017' AND '3/7/2017'
GROUP by REQUIRED.REQ_TOTAL, THIS_OFFICE_ID, PUSHSTAT_DATE
ORDER by REQUIRED.REQ_TOTAL desc, THIS_OFFICE_ID, PUSHSTAT_DATE;
OFFICE_ID TRANSACTION_DATE TRANSACTION_CNT TOTAL_SUMMA
(1) Stockholm 03Mar17 5000 24000
(2) Stockholm 04Mar17 6000 24000
(3) Stockholm 05Mar17 7000 24000
(4) Stockholm 06Mar17 3000 24000
(5) Stockholm 07Mar17 3000 24000
(6) Kiruna 03Mar17 4000 22000
(7) Kiruna 04Mar17 5000 22000
(8) Kiruna 05Mar17 6000 22000
(9) Kiruna 07Mar17 7000 22000
(10) Copenhagen 03Mar17 1000 6000
(11) Copenhagen 04Mar17 5000 6000
Stockholm and Kiruna are returned but not Copenhagen. That's fine, working as designed.
I would like to expand this statement to also return the days containing data for each group, i.e. 5 for Stockholm and 4 for Kiruna. So it doesn't work to just compute max(date) minus min(date) since usually there are missing days in between.
I suspect count(TRANSACTION_CNT) for each group must be involved. Thanks in advance for all suggestions.