CODEWITHSUNDEEP
sqloracle-database
nurman syah
nurman syah

Reputation: 87

COUNT WITH 2 CONDITIONS IN ORACLE

I was trying so hard to make a query from 1 table with 2 conditions. And every condition should be able to count the row based on transaction date.

QUERY 1:

SELECT 
  INST_NO,
  TRUNC(INS_TS)INS_TS,
  COUNT(*) TOTAL_INSPECTION
FROM TMCI_QC_LINE2
WHERE INST_NO='M190200772'
GROUP BY 
    INST_NO, 
    TRUNC(INS_TS)

RESULT OF QUERY 1:

INST_NO      INS_TS      TOTAL_INSPECTION
M190200772   18-FEB-19         8 
M190200772   19-FEB-19         3

QUERY 2:

SELECT 
  INST_NO,
  TRUNC(INS_TS)INS_TS,
  COUNT(*) CASE_INSPECTION
FROM TMCI_QC_LINE2 
WHERE INST_NO='M190200772' AND QTY_OK IN (0)
GROUP BY
    INST_NO,
    TRUNC(INS_TS)

RESULT OF QUERY 2: Table 2:

INST_NO      INS_TS      CASE_INSPECTION
M190200772   18-FEB-19          4 
M190200772   19-FEB-19          2

Now, I would like the results to look like this:

INST_NO      INS_TS      INSPECTION    CASE
M190200772   18-FEB-19       8           4
M190200772   19-FEB-19       3           2

Upvotes: 1

Views: 2355

Answers (1)

forpas
forpas

Reputation: 164064

You need the 1st query and conditional aggregation for the new column:

SELECT 
  INST_NO,
  TRUNC(INS_TS) INS_TS, 
  COUNT(*) INSPECTION,
  SUM(CASE WHEN QTY_OK = 0 THEN 1 ELSE 0 END) CASE
FROM TMCI_QC_LINE2
WHERE INST_NO='M190200772'
GROUP BY INST_NO, TRUNC(INS_TS)

Upvotes: 2

Related Questions