CODEWITHSUNDEEP
sql
WAQ
WAQ

Reputation: 2626

Query to fetch summarized information from multiple tables

I have below query to fetch some information from the database.

select a.PIPE_SYS, c.WELD_TYPE, c.RT, sum(a.W_C) welded, 
count(d.RT_CMP_DATE) Tested, count(a.RT_STATUS) Accepted
from Weld_master a, Weld_names b, NDT_PER_CATEGORY_ c, Nd_Test d 
where a.WELD_NAME = b.WELD_NAME and b.TYPE = c.WELD_TYPE and a.NDT_CATEGORY = 
c.CATEGORY  and a.project_no = d.proj_no and a.dwg_no = d.dwg_no and 
a.Spool_No = d.Spool_No and a.weld_no = d.weld_no group by a.PIPE_SYS, c.rt, 
c.WELD_TYPE;

Above gives me the correct result as I expect except for Accepted field. Since, the data in my database is saved in a way that by default RT_STATUS field is set to a certain value (non null) and when RT status of a weld is Accepted or Rejected then RT_STATUS field is updated with relevant value in database.

So, in above query, how can I get count of Accepted and Rejected RT_STATUS field.

Upvotes: 0

Views: 40

Answers (1)

Helen Gore
Helen Gore

Reputation: 81

You could try something like 

sum(case when a.RT_STATUS = 'Accepted' then 1 else 0 end) AS AcceptedCount,
sum(case when a.RT_STATUS = 'Rejected' then 1 else 0 end) AS RejectedCount

Upvotes: 1

Related Questions