Home » SQL & PL/SQL » SQL & PL/SQL » SQL query (oracle xe 11g)
SQL query [message #667561] Fri, 05 January 2018 07:24 Go to next message
hudo
Messages: 163
Registered: May 2004
Senior Member
A table contains the following entries
CREATE TABLE PROV(
ID NUMBER,
PROVIDER VARCHAR2(30),
AMOUNT NUMBER
);


INSERT INTO PROV (ID,PROVIDER, AMOUNT) VALUES ( 1, 'VW Salzgitter', 10 );
INSERT INTO PROV (ID,PROVIDER, AMOUNT) VALUES( 2, 'VW Wolfsburg', 4 );
INSERT INTO PROV (ID,PROVIDER, AMOUNT) VALUES( 3, 'VW Hannover', 3 );
INSERT INTO PROV (ID,PROVIDER, AMOUNT) VALUES( 4, 'VW Braunschweig', 5 );
INSERT INTO PROV (ID,PROVIDER, AMOUNT) VALUES( 5, 'VW Salzgitter', 3 );
INSERT INTO PROV (ID,PROVIDER, AMOUNT) VALUES( 6, 'DB Mannheim', 8 );
INSERT INTO PROV (ID,PROVIDER, AMOUNT) VALUES( 7, 'DB Gaggenau', 2 );
INSERT INTO PROV (ID,PROVIDER, AMOUNT) VALUES( 8, 'DB Mannheim', 3 );
INSERT INTO PROV (ID,PROVIDER, AMOUNT) VALUES( 9, 'Audi Gyoer', 6 );
INSERT INTO PROV (ID,PROVIDER, AMOUNT) VALUES( 10, 'Audi Ingolstadt', 7 );


I like to have now the following result

Quote:

PROVIDER|ANZ
VW|5
DB|3
Audi|2

where ANZ is the sum of all data records of a car manufacturer independent of the location.

Sorry for the insignificant title.

Re: SQL query [message #667566 is a reply to message #667561] Fri, 05 January 2018 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just COUNT the rows GROUPing them by the car manufacturer which seems to be the first word of PROVIDER (use SUBSTR and INSTR).

Re: SQL query [message #667567 is a reply to message #667561] Fri, 05 January 2018 08:12 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well that's not a great data model - manufacturer and location should be in separate columns.
Use instr to find the space.
Use substr to get the characters up to the space.
They use group by and count(*) to get the result you want.

EDIT: separate columns

[Updated on: Fri, 05 January 2018 10:05]

Report message to a moderator

Re: SQL query [message #667573 is a reply to message #667566] Fri, 05 January 2018 09:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2948
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Fri, 05 January 2018 09:12

Just COUNT the rows GROUPing them by the car manufacturer which seems to be the first word of PROVIDER (use SUBSTR and INSTR).

Bad design and SUBSTR/INSTR might not help:

'Alfa Romeo Stelvio'
'De Tomaso Mangusta'
'Royal Enfield Classic 350'
'Rolls-Royce Silver Spur'
'Elio Motors Reliant Robin'

SY.
Re: SQL query [message #667577 is a reply to message #667573] Fri, 05 January 2018 10:54 Go to previous message
Michel Cadot
Messages: 67132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
... SUBSTR/INSTR might not help:

With the restriction I mentioned it does, now let's OP tell the story.
Anyway, it is a bad design.

@OP, read Normalization.

Previous Topic: printing first day of every month
Next Topic: Recording Update through trigger based on csv file upload
Goto Forum:
  


Current Time: Tue May 26 23:00:05 CDT 2020