CODEWITHSUNDEEP
sqlsql-server
Ram_God
Ram_God

Reputation: 47

SQL server: retrieve grouped data from a table

Write SQL query based on info below Table TRACK_BY_WCN

CREATE TABLE CONTRACTOPS.TRACK_BY_WCN(
 CLAIM_TYPE     CHAR(1 BYTE)
,LOBID          CHAR(3 BYTE)
,WCN            VARCHAR2(10 BYTE)
,RECEIVED_DATE  DATE
,FOUND_DATE     DATE
,CLAIM_NUMBER   VARCHAR2(10 BYTE) DEFAULT NULL
,HOLD_FLAG      CHAR(1 BYTE)      DEFAULT NULL
,LOCK_FLAG      VARCHAR2(3 BYTE)  DEFAULT NULL
,BILLED         NUMBER(16,2))

There are data in first 3 columns, CLAIM_TYPE, LOBID, WCN, and there is program doing match by WCN. When found, the program will update all other fields. Please use one SQL to get count on how many WCN and how many matched claims, by claim type.

Upvotes: 0

Views: 113

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

It's hard to tell what you are asking exactly. This might be what you want:

Count rows per WCN:

SELECT WCN, count(*) AS row_count
FROM   CONTRACTOPS.TRACK_BY_WCN
GROUP  BY WCN;

Count rows per CLAIM_TYPE

SELECT CLAIM_TYPE, count(*) AS row_count
FROM   CONTRACTOPS.TRACK_BY_WCN
GROUP  BY CLAIM_TYPE;

Count claims per WCN and CLAIM_TYPE

SELECT WCN, CLAIM_TYPE, count(*) AS row_count
FROM   CONTRACTOPS.TRACK_BY_WCN
GROUP  BY WCN, CLAIM_TYPE;

Upvotes: 2

Related Questions