?
Solved

SQL : Query question, how to count total

Posted on 2011-09-02
3
Medium Priority
?
565 Views
Last Modified: 2012-05-12
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
Comment
Question by:boon86
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36472920

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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 36472929
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
 
LVL 7

Author Comment

by:boon86
ID: 36472957
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question