CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
Arav
Arav

Reputation: 5247

SQL Query group by

Pasted the sql query that is being used currently to generate the report from unix shell script. I want to have an addition column COUNT (cdw.file_id) writeoffcnt to the below sql query which displays the count of the the record that matches mg_disp_status=0 and mig_disp_code =3 . The existing field COUNT (cdw.file_id) cnt should have the count of record that matches mg_disp_status = 1 and mig_disp_code <> 2. How can i modify the query?

 SELECT fs.file_id,
                 fs.file_id_serv,
                 fs.file_process_dt,
                 fs.file_name,
                 fs.total_records,
                 RTRIM (d.description_text) source,
                 SUM(amount),
                 COUNT (cdw.file_id) cnt
            FROM file_status fs,
                 dr_data_work cdw,
                 descriptions d,
                 contacts ec
           WHERE file_process_dt >= TO_DATE ('${START_DATE}', 'DD-MON-YYYY')
                 AND  file_process_dt < TO_DATE ('${END_DATE}', 'DD-MON-YYYY')
                 AND fs.ext_contact_id = ec.ext_contact_id
                 --
                 AND ec.description_code = d.description_code
                 AND cdw.file_id = fs.file_id
                 AND mg_disp_status = 1
                 AND mig_disp_code <> 2
        GROUP BY fs.file_id,
                 fs.file_id_serv,
                 fs.file_process_dt,
                 fs.file_name,
                 fs.total_records,
                 RTRIM (d.description_text);

Upvotes: 0

Views: 232

Answers (2)

Rob Paterson
Rob Paterson

Reputation: 2593

I don't fully understand all your permutations of requirements, but something like the following should work:

SELECT
.
.
.
SUM(CASE mg_disp_status=0 and mig_disp_code =3 THEN 1 ELSE 0 END) cnt1,
SUM(CASE mg_disp_status=1 and mig_disp_code <> 2 THEN 1 ELSE 0 END) cnt2,

Upvotes: 3

thule
thule

Reputation: 4222

Join twice with different aliases and do your counts:

SELECT fs.file_id,
             fs.file_id_serv,
             fs.file_process_dt,
             fs.file_name,
             fs.total_records,
             RTRIM (d.description_text) source,
             SUM(amount),
             COUNT (cdw.file_id) cnt1, COUNT (cdw2.file_id) cnt2
        FROM file_status fs,
             dr_data_work cdw, dr_data_work cdw2,
             descriptions d,
             contacts ec
       WHERE file_process_dt >= TO_DATE ('${START_DATE}', 'DD-MON-YYYY')
             AND  file_process_dt < TO_DATE ('${END_DATE}', 'DD-MON-YYYY')
             AND fs.ext_contact_id = ec.ext_contact_id
             --
             AND ec.description_code = d.description_code
             AND cdw.file_id = fs.file_id AND cdw2.file_id = fs.file_id
             AND cdw.mg_disp_status = 1 AND cdw2.mg_disp_status = 0 
             AND cdw.mig_disp_code <> 2 AND cdw2.mg_disp_code = 3



    GROUP BY fs.file_id,
             fs.file_id_serv,
             fs.file_process_dt,
             fs.file_name,
             fs.total_records,
             RTRIM (d.description_text);

Upvotes: 0

Related Questions