No question at this time
DBA Top 10
1 M. Cadot 6300
2 B. Vroman 2700
3 P. Tsongayinwe 1500
4 P. Wisse 1300
4 J. Péran 1300
6 A. Kavsek 900
7 D. Walgude 400
7 J. Schnackenberg 400
9 J. Alcroft 200
10 A. Hudspith 100
10 L. Ywema 100
10 D. Johnson 100
10 B. B 100
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48709
Total active users1322
Act. users last 24h2
Act. users last hour0
Registered user hits last week33
Registered user hits last month217
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, 258 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, 257 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, 257 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, 256 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, 255 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, 255 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