asked on

SQL : Query question, how to count total

I have 3 table they look like this:

Table 1: OPERATION

OPERATE_ID | OPERATE_TYPE | COUNTRY_ID
---------------------------------------------------------
1 | A | 2
2 | C | 2
3 | B | 3
4 | A | 2

Table 2: CASE

CASE_ID | PROFAIL_ID | OPERATE_ID
------------------------------------
1 | 3 | 2
2 | 2 | 2
3 | 3 | 2
4 | 1 | 1
5 | 3 | 1

Table 3: PROFAIL

PROFAIL_ID | NAME | NATIONALITY
-------------------------------------
1 | Alex | Local
2 | Owen | Foreign
3 | Ben | Local

SQL DUMP ( in case you need to test )

-- ----------------------------
-- Table structure for "CCS"."CASE_KES"
-- ----------------------------
DROP TABLE "CCS"."CASE_KES";
CREATE TABLE "CCS"."CASE_KES" (
"CASE_ID" NUMBER NOT NULL ,
"PROFAIL_ID" NUMBER NULL ,
"OPERATE_ID" NUMBER NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of CASE_KES
-- ----------------------------
INSERT INTO "CCS"."CASE_KES" VALUES ('1', '3', '2');
INSERT INTO "CCS"."CASE_KES" VALUES ('2', '2', '2');
INSERT INTO "CCS"."CASE_KES" VALUES ('3', '3', '2');
INSERT INTO "CCS"."CASE_KES" VALUES ('4', '1', '1');
INSERT INTO "CCS"."CASE_KES" VALUES ('5', '3', '1');

-- ----------------------------
-- Table structure for "CCS"."COUNTRY"
-- ----------------------------
DROP TABLE "CCS"."COUNTRY";
CREATE TABLE "CCS"."COUNTRY" (
"COUNTRY_ID" VARCHAR2(10 BYTE) NOT NULL ,
"COUNTRY_NAME" VARCHAR2(200 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of COUNTRY
-- ----------------------------
INSERT INTO "CCS"."COUNTRY" VALUES ('1', 'Malaysia');
INSERT INTO "CCS"."COUNTRY" VALUES ('2', 'America');
INSERT INTO "CCS"."COUNTRY" VALUES ('3', 'Japan');

-- ----------------------------
-- Table structure for "CCS"."OPERATION"
-- ----------------------------
DROP TABLE "CCS"."OPERATION";
CREATE TABLE "CCS"."OPERATION" (
"OPERATE_ID" NUMBER NOT NULL ,
"OPERATE_TYPE" VARCHAR2(16 BYTE) NULL ,
"COUNTRY_ID" NUMBER NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of OPERATION
-- ----------------------------
INSERT INTO "CCS"."OPERATION" VALUES ('1', 'A', '2');
INSERT INTO "CCS"."OPERATION" VALUES ('2', 'C', '2');
INSERT INTO "CCS"."OPERATION" VALUES ('3', 'B', '3');
INSERT INTO "CCS"."OPERATION" VALUES ('4', 'A', '2');

-- ----------------------------
-- Table structure for "CCS"."PROFAIL"
-- ----------------------------
DROP TABLE "CCS"."PROFAIL";
CREATE TABLE "CCS"."PROFAIL" (
"PROFAIL_ID" VARCHAR2(16 BYTE) NOT NULL ,
"NAME" VARCHAR2(200 BYTE) NULL ,
"NATIONALITY" VARCHAR2(200 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of PROFAIL
-- ----------------------------
INSERT INTO "CCS"."PROFAIL" VALUES ('1', 'Alex', 'Local');
INSERT INTO "CCS"."PROFAIL" VALUES ('2', 'Owen', 'Foreign');
INSERT INTO "CCS"."PROFAIL" VALUES ('3', 'Ben', 'Local');

-- ----------------------------
-- Indexes structure for table CASE_KES
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table "CCS"."CASE_KES"
-- ----------------------------
ALTER TABLE "CCS"."CASE_KES" ADD PRIMARY KEY ("CASE_ID");

-- ----------------------------
-- Indexes structure for table COUNTRY
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table "CCS"."COUNTRY"
-- ----------------------------
ALTER TABLE "CCS"."COUNTRY" ADD PRIMARY KEY ("COUNTRY_ID");

-- ----------------------------
-- Checks structure for table "CCS"."OPERATION"
-- ----------------------------
ALTER TABLE "CCS"."OPERATION" ADD CHECK ("OPERATE_ID" IS NOT NULL);

-- ----------------------------
-- Indexes structure for table PROFAIL
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table "CCS"."PROFAIL"
-- ----------------------------
ALTER TABLE "CCS"."PROFAIL" ADD PRIMARY KEY ("PROFAIL_ID");

Open in new window

My Working Query:

SELECT   country_name country,
         COUNT(DISTINCT CASE WHEN operate_type = 'A' THEN operation.operate_id END) operation_a,
         COUNT(DISTINCT CASE WHEN operate_type = 'B' THEN operation.operate_id END) operation_b,
         COUNT(DISTINCT CASE WHEN operate_type = 'C' THEN operation.operate_id END) operation_c,
           COUNT(DISTINCT CASE WHEN operate_type = 'A' THEN operation.operate_id END)
         + COUNT(DISTINCT CASE WHEN operate_type = 'B' THEN operation.operate_id END)
         + COUNT(DISTINCT CASE WHEN operate_type = 'C' THEN operation.operate_id END)
             total_operations,
         COUNT(DISTINCT CASE WHEN nationality = 'Local' THEN CASE.case_id END) local_case,
         COUNT(DISTINCT CASE WHEN nationality = 'Foreign' THEN CASE.case_id END) foreign_case,
         COUNT(DISTINCT CASE WHEN nationality = 'Local' THEN CASE.case_id END)
         + COUNT(DISTINCT CASE WHEN nationality = 'Foreign' THEN CASE.case_id END)
             total_cases
    FROM country
        LEFT JOIN operation
            ON country.country_id = operation.country_id
        LEFT JOIN CASE
            ON operation.operate_id = CASE.operate_id
        LEFT JOIN profail
            ON profail.profail_id = CASE.profail_id
WHERE country.country_id in (2,3)   -- or use name filter
GROUP BY country_name
ORDER BY country_name

Open in new window

Problem: How to calculate all the total value, please refer image that i attached below

should i combine the total query to my working query or separate it to 2 different query?
any suggestion?

thanks
tableOperation.png

Sean Stuber

I changed the table name in the query to CASE_KES based on your table creation script above

SELECT country_name country,
COUNT(DISTINCT CASE WHEN operate_type = 'A' THEN operation.operate_id END) operation_a,
COUNT(DISTINCT CASE WHEN operate_type = 'B' THEN operation.operate_id END) operation_b,
COUNT(DISTINCT CASE WHEN operate_type = 'C' THEN operation.operate_id END) operation_c,
COUNT(DISTINCT CASE WHEN operate_type = 'A' THEN operation.operate_id END)
+ COUNT(DISTINCT CASE WHEN operate_type = 'B' THEN operation.operate_id END)
+ COUNT(DISTINCT CASE WHEN operate_type = 'C' THEN operation.operate_id END)
total_operations,
COUNT(DISTINCT CASE WHEN nationality = 'Local' THEN case_kes.case_id END) local_case,
COUNT(DISTINCT CASE WHEN nationality = 'Foreign' THEN case_kes.case_id END) foreign_case,
COUNT(DISTINCT CASE WHEN nationality = 'Local' THEN case_kes.case_id END)
+ COUNT(DISTINCT CASE WHEN nationality = 'Foreign' THEN case_kes.case_id END)
total_cases
FROM country
LEFT JOIN operation
ON country.country_id = operation.country_id
LEFT JOIN CASE_kes
ON operation.operate_id = CASE_kes.operate_id
LEFT JOIN profail
ON profail.profail_id = CASE_kes.profail_id
WHERE country.country_id in (2,3) -- or use name filter
GROUP BY rollup(country_name)
ORDER BY country_name

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

boon86

ASKER

thank you its work great, you are awesome, i changed my CASE table to :

CASE_KES to avoid confusion with the CASE of DISTINCT