SEO

CREATE TABLE SQL_TABL (
ORD_DATE Date,
PROD_CODE Varchar2(10),
CUST_CODE Varchar2(10),
QTY Number(2),
UNIT_PRICE Number(5,2),
SHIP_DATE Date,
CARR_CODE Varchar2(12),
SHIP_COST Number(5,2),
PMT_DATE Date
);

insert into SQL_TABL values ('08-Jan-05', 'A-50-0013', 'Cu-08-143',
4,
181.79, '09-Jan-05', 'TransCo', 60.27, '15-Jan-05');
insert into SQL_TABL values ('15-Jan-05', 'A-25-0753', 'Cu-08-180',
13, 265.61, '23-Jan-05', 'Expedit', 278.36, '29-Jan-05');
insert into SQL_TABL values ('19-Jan-05', 'A-24-0101', 'Cu-08-133',
20, 120.18, '22-Jan-05', 'Expedit', 188.28, '29-Jan-05');
insert into SQL_ASSGN_2 values ('30-Jan-05', 'B-63-0023', 'Cu-4-078',
4,
280.88, '09-Feb-05', 'Expedit', 92.93, '04-Feb-05');


hi every one i have a a table like such and i want to do 2 things

1.Produce total sales per Product Group interval (0-9, 10-19 etc) for
sales made in 2006. Report only those groups which overall have total
sales greater than 1500.

2.Produce total sales per Product Group interval (0-9, 10-19 etc) for
sales made in 2006. Report only those groups where sales is more than
10% of grand total of sales.

*** the group code is the 2 digits between the - sign in the prod
code
i.e. A-50-0123 is of group 50
Group codes from 0 to 9 form Group0. Codes from 10 to 19 form Group10
etc.
i know from excel that i need a rounding function to get them, but i
dont know how to imply here
in oracle

Any clues please on any of the two problems??
thanks in advance

On Dec 3, 7:34 pm, TheGodfather <saliba.toufic.geo...@gmail.com>
wrote:
> CREATE TABLE SQL_TABL (
> ORD_DATE Date,
> PROD_CODE Varchar2(10),
> CUST_CODE Varchar2(10),
> QTY Number(2),
> UNIT_PRICE Number(5,2),
> SHIP_DATE Date,
> CARR_CODE Varchar2(12),
> SHIP_COST Number(5,2),
> PMT_DATE Date
> );
>
> insert into SQL_TABL values ('08-Jan-05', 'A-50-0013', 'Cu-08-143',
> 4,
> 181.79, '09-Jan-05', 'TransCo', 60.27, '15-Jan-05');
> insert into SQL_TABL values ('15-Jan-05', 'A-25-0753', 'Cu-08-180',
> 13, 265.61, '23-Jan-05', 'Expedit', 278.36, '29-Jan-05');
> insert into SQL_TABL values ('19-Jan-05', 'A-24-0101', 'Cu-08-133',
> 20, 120.18, '22-Jan-05', 'Expedit', 188.28, '29-Jan-05');
> insert into SQL_ASSGN_2 values ('30-Jan-05', 'B-63-0023', 'Cu-4-078',
> 4,
> 280.88, '09-Feb-05', 'Expedit', 92.93, '04-Feb-05');
>
> hi every one i have a a table like such and i want to do 2 things
>
> 1.Produce total sales per Product Group interval (0-9, 10-19 etc) for
> sales made in 2006. Report only those groups which overall have total
> sales greater than 1500.
>
> 2.Produce total sales per Product Group interval (0-9, 10-19 etc) for
> sales made in 2006. Report only those groups where sales is more than
> 10% of grand total of sales.
>
> *** the group code is the 2 digits between the - sign in the prod
> code
> i.e. A-50-0123 is of group 50
> Group codes from 0 to 9 form Group0. Codes from 10 to 19 form Group10
> etc.
> i know from excel that i need a rounding function to get them, but i
> dont know how to imply here
> in oracle
>
> Any clues please on any of the two problems??
> thanks in advance

I am having trouble understanding what the motivation is for these
questions. Are you learning SQL is a class, or are these actual
business requirements? There are a couple very good SQL books on the
market that will help you with these concepts. I will provide you
with a couple hints to help you get started:
#1 First, use the function that retrieves the portion of the PROD_CODE
column that is of interest, which likely starts at character position
3 and extends for 2 characters. Then, convert the portion of the
PROD_CODE column to a number and divide by ten. You will then need to
use the function that rounds the result down, or truncates the decimal
portion of the result. Group on this column, and then use a HAVING
clause to restrict the grouped rows that are returned.

#2 A little more complicated. Any restrictions on using analytical
functions, subqueries, or inline views? 10% is the SUM of all values
divided by 10.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

thanks Charles this is the second time you help me , i appreciate it
those are excel files that are to be put in oracle, i am very good in
doing those things in excel
and VBA but since the job is switching to SQL plus i have to adopt
myself as you see , i have no knowledge at all
in SQL, i am using a book named " sql for dummies " this is it.
cheers

TheGodfather wrote:
> thanks Charles this is the second time you help me , i appreciate it
> those are excel files that are to be put in oracle, i am very good in
> doing those things in excel
> and VBA but since the job is switching to SQL plus i have to adopt
> myself as you see , i have no knowledge at all
> in SQL, i am using a book named " sql for dummies " this is it.
> cheers
http://tahiti.oracle.com

Select the version of your choice (Express Edition, or "XE" may
be a good start - nice small installer, no fiddling/configuring,
web interface), and read the "2 day DBA".
Or the SQL reference Guide.

Concepts is always a good starter.

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...