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
Solved

SQL : Query question, how to count total

Posted on 2011-09-02
3
547 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
  • 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 500 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

808 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