?
Solved

SQL : Query question, how to count total

Posted on 2011-09-02
3
Medium Priority
?
576 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

650 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