Home » SQL & PL/SQL » SQL & PL/SQL » How to count different items with one sentence instead of above? (ORACLE)
| How to count different items with one sentence instead of above? [message #600316] |
Mon, 04 November 2013 22:05  |
 |
zhaoquer
Messages: 35
Registered: October 2013
|
Member |
|
|
CREATE TABLE "CIM_TEST"
(
"NAME" VARCHAR2(60),
"COUNTRY" VARCHAR2(100),
"CITY" VARCHAR2(100),
"TOWN" VARCHAR2(100),
"SIGN_STATUS" CHAR(1) DEFAULT 0
);
insert into cim_test (NAME, COUNTRY, CITY, TOWN, SIGN_STATUS)values ('N1', 'CHINA', 'SH', 'T1', '1');
insert into cim_test (NAME, COUNTRY, CITY, TOWN, SIGN_STATUS)values ('N2', 'CHINA', 'SH', 'T2', '0');
insert into cim_test (NAME, COUNTRY, CITY, TOWN, SIGN_STATUS)values ('N5', 'U.S.A', 'NY', 'T5', '1');
insert into cim_test (NAME, COUNTRY, CITY, TOWN, SIGN_STATUS)values ('N6', 'U.S.A', 'Washington', 'T6', '0');
insert into cim_test (NAME, COUNTRY, CITY, TOWN, SIGN_STATUS)values ('N3', 'CHINA', 'BJ', 'T3', '1');
insert into cim_test (NAME, COUNTRY, CITY, TOWN, SIGN_STATUS)values ('N4', 'U.S.A', 'NY', 'T4', '0');
insert into cim_test (NAME, COUNTRY, CITY, TOWN, SIGN_STATUS)values ('N8', 'CHINA', 'SH', 'T8', '0');
insert into cim_test (NAME, COUNTRY, CITY, TOWN, SIGN_STATUS)values ('N7', 'U.S.A', 'NY', 'T7', '1');
commit;
SELECT COUNT (*),c.country from cim_test c where c.sign_status='0' group by c.country;
SELECT COUNT (*),c.city from cim_test c where c.sign_status='0' group by c.city;
SELECT COUNT (*),c.town from cim_test c where c.sign_status='0' group by c.town;
SELECT COUNT (*),c.country from cim_test c where c.sign_status='1' group by c.country;
SELECT COUNT (*),c.city from cim_test c where c.sign_status='1' group by c.city;
SELECT COUNT (*),c.town from cim_test c where c.sign_status='1' group by c.town;
How to count different items with one sentence instead of above? Thanks in advance!
|
|
|
|
| Re: How to count different items with one sentence instead of above? [message #600317 is a reply to message #600316] |
Mon, 04 November 2013 22:45  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Are you looking for INLINE VIEW. You will have the result set side-by-side, however, I don't see any relationship between the country, city and town. So you will get the product of all rows in the result set.
I have not tested this, just see if you are looking for something like this :
SELECT *
FROM (SELECT COUNT(*), C.COUNTRY
FROM CIM_TEST C
WHERE C.SIGN_STATUS = '0'
GROUP BY C.COUNTRY) COUNTRY,
(SELECT COUNT(*), C.CITY
FROM CIM_TEST C
WHERE C.SIGN_STATUS = '0'
GROUP BY C.CITY) CITY,
(SELECT COUNT(*), C.TOWN
FROM CIM_TEST C
WHERE C.SIGN_STATUS = '0'
GROUP BY C.TOWN) TOWN;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to count different items with one sentence instead of above? [message #600435 is a reply to message #600430] |
Wed, 06 November 2013 12:21 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
manubatham20 wrote on Wed, 06 November 2013 22:34Lalit,
I am not sure on this, but the reason may be you should treat every number as varchar2, if you are not applying numerical operations on that, because it costs you memory.
It doesn't matter if an arithmetic operation is done or not, however, declaring a number as character and later using a number data type as character is a design flaw. Internally Oracle will have an implicit data type conversion, which is an overhead to the cost for the execution path to convert number to character. Is it really needed? If yes, then why?
|
|
|
|
| Re: How to count different items with one sentence instead of above? [message #600438 is a reply to message #600435] |
Wed, 06 November 2013 12:36 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Lalit Kumar B wrote on Wed, 06 November 2013 18:21It doesn't matter if an arithmetic operation is done or not, however, declaring a number as character and later using a number data type as character is a design flaw.
OK, Agree with above.
But in this particular case, SIGN_STATUS is char all the time. Why Oracle will do a automatic conversion, when we are comparing Char with Char, how would declaring it number will make any difference?
Regards,
Manu
[Updated on: Wed, 06 November 2013 12:38] Report message to a moderator |
|
|
|
| Re: How to count different items with one sentence instead of above? [message #600449 is a reply to message #600435] |
Wed, 06 November 2013 15:00 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Lalit Kumar B wrote on Wed, 06 November 2013 18:21manubatham20 wrote on Wed, 06 November 2013 22:34Lalit,
I am not sure on this, but the reason may be you should treat every number as varchar2, if you are not applying numerical operations on that, because it costs you memory.
It doesn't matter if an arithmetic operation is done or not, however, declaring a number as character and later using a number data type as character is a design flaw. Internally Oracle will have an implicit data type conversion, which is an overhead to the cost for the execution path to convert number to character. Is it really needed? If yes, then why?
The datatype is char and the sql treats it as char, so where exactly is implicit conversion supposed to come into it?
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 14:55:15 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
