• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 582
  • Last Modified:

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
0
boon86
Asked:
boon86
  • 2
1 Solution
 
sdstuberCommented:

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
0
 
sdstuberCommented:
same idea, but labels the TOTAL line as such, instead of just returning NULL for that name


SELECT   DECODE(GROUPING_ID(country_name), 1, 'TOTAL', 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 GROUPING_ID(country_name), country_name
0
 
boon86Author Commented:
thank you its work great, you are awesome, i changed my CASE table to :

CASE_KES to avoid confusion with the CASE of DISTINCT
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now