No question at this time
DBA Top 10
1 M. Cadot 6600
2 B. Vroman 3400
3 J. Péran 2200
4 A. Kavsek 1400
5 P. Tsongayinwe 900
6 P. Wisse 600
7 T. Boles 400
7 D. Walgude 400
7 D. Johnson 400
10 J. Alcroft 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48675
Total active users1328
Act. users last 24h0
Act. users last hour0
Registered user hits last week24
Registered user hits last month452
Go up

SQL Query help
Next thread: Help needed to delete huge volume of data
Prev thread: McOptions

Message Score Author Date
Hi there, Need some help to write a sql query o...... S SPB Mar 15, 2022, 16:52
Oracle version IS applicable as query depends on...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Mar 15, 2022, 19:03
Hello, And I should add : this is a bad thing y...... Joël Péran Mar 16, 2022, 07:44
Thanks Michel, Here is the create table stateme...... S SPB Mar 17, 2022, 18:30
<pre>SQL> with 2 asked as ( select upper('&c...... Score: 1000 PtsScore: 1000 PtsScore: 1000 PtsScore: 1000 PtsScore: 1000 Pts Michel Cadot Mar 17, 2022, 18:51
Great thanks Michel, You read my mind. This is...... S SPB Mar 18, 2022, 06:19

Follow up by mail Click here


Subject: SQL Query help
Author: S SPB, India
Date: Mar 15, 2022, 16:52, 109 days ago
Os info: Not Applicable
Oracle info: Not Applicable
Message: Hi there,

Need some help to write a sql query on below conditions.

My table has Customer Name and City Names with below data.

CUSTOMER CITY
--------- ---------
ALBERT NewYork
IBRAHIM Dallas
SUNNY Washington
JOHN NewYork
SMITH NewYork
RAJATH NewYork
RAMANA SanJose

I need to print the output as below..

City, Total People in that City, Percentage of people in the City from overall. Also If I query about any non-existing city from above list, it should return zero in all above results since its non-existing but city name should be showing in the first field. Like below.

City Total Percent
ISABELA 0 0%


Can anyone help me out on this? Thankyou

SSPB
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SQL Query help
Author: Michel Cadot, France
Date: Mar 15, 2022, 19:03, 109 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
Oracle version IS applicable as query depends on this version.

In addition, post create table and insert statements for your example to help up to help you.
And use pre tags as explained in the "Formatting" at top of "Reply" form.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SQL Query help
Author: Joël Péran, France
Date: Mar 16, 2022, 07:44, 109 days ago
Message: Hello,

And I should add : this is a bad thing you did not read the advice about presenting your request, because the solution you are asking is not quite difficult when using analytical functions ... At least, you have a clue :)
Regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SQL Query help
Author: S SPB, India
Date: Mar 17, 2022, 18:30, 107 days ago
Message: Thanks Michel,

Here is the create table statement and the Insert data statements. When we search for a non-existing City like CHICAGO, it should show the City Name as CHICAGO, but the count of people staying there in CHICAGO and the overall percentage should show 0.



CREATE TABLE CUSTOMERS (
CUSTOMER VARCHAR2(32),
CITY VARCHAR2(32));

INSERT INTO CUSTOMERS VALUES('ALBERT','NEWYORK');
INSERT INTO CUSTOMERS VALUES('IBRAHIM','DALLAS');
INSERT INTO CUSTOMERS VALUES('SUNNY','WASHINGTON');
INSERT INTO CUSTOMERS VALUES('JOHN','NEWYORK');
INSERT INTO CUSTOMERS VALUES('SMITH','NEWYORK');
INSERT INTO CUSTOMERS VALUES('RAJATH','NEWYORK');
INSERT INTO CUSTOMERS VALUES('RAMANA','SANJOSE');
COMMIT;



Thanks a ton,
SSP
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SQL Query help
Author: Michel Cadot, France
Date: Mar 17, 2022, 18:51, 107 days ago
Score:   Score: 1000 PtsScore: 1000 PtsScore: 1000 PtsScore: 1000 PtsScore: 1000 Pts
Message:
SQL> with

2 asked as ( select upper('&city') asked from dual),
3 data as (
4 select city, count(*) total, 100*ratio_to_report(count(*)) over() pct
5 from customers
6 group by city
7 )
8 select asked city, nvl(total,0) total, round(nvl(pct,0),2) "%"
9 from asked left outer join data on city = asked
10 /
Enter value for city: NEWYORK
CITY TOTAL %
------- ---------- ----------
NEWYORK 4 57.14

1 row selected.

SQL> /
Enter value for city: CHICAGO
CITY TOTAL %
------- ---------- ----------
CHICAGO 0 0



Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SQL Query help
Author: S SPB, India
Date: Mar 18, 2022, 06:19, 107 days ago
Message: Great thanks Michel,

You read my mind. This is exactly what I am looking for.
No doubt that's why you are on top of the list :-)

Have a great time..

SSPB
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here