Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: any thoughts please?!

Re: any thoughts please?!

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sun, 2 Dec 2007 13:14:21 -0800 (PST)
Message-ID: <684a6012-bc5b-4dd2-84a2-1260a3ac917b@t47g2000hsc.googlegroups.com>


On Dec 2, 2:57 pm, TheGodfather <saliba.toufic.geo..._at_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');
>
> hi every one i have a atable like such and many things to do on it,
> since i am new to sql and i know how important it is, i want you
> please to help if you see me posting again and agian dont get bored
> with me please, i ll be posting my attempts and whoever wants can
> help me, as a first task i want to "Produce total sales per Product
> Group,per year"the product group is the middle numbers as '50' for
> the first one '25for the second, so i thought those must be distinct
> since they are the key so i made the following:
>
> select to_char(ORD_DATE,'yyyy'),distinct( substr (PROD_CODE,3,2)),
> ((QTY*UNIT_PRICE)+SHIP_COST) from TABLE,
>
> but this doesnt do the job becoz it wont give me the distinct values
> but duplicates and thats what i want
> you see my attempt can u help me?
> thanks

First of all, thanks for posting the SQL statements to create the table and insert rows into the table.

You are close, but are heading in the wrong direction with the DISTINCT keyword. GROUP BY produces a distinct list of values, and it can be used with COUNT, MIN, MAX, and SUM. First, let's take a look at your SQL statement without the DISTINCT clause and without the sales calculation:
SELECT
  TO_CHAR(ORD_DATE,'YYYY') ORDER_YEAR,
  SUBSTR(PROD_CODE,3,2) PRODUCT_GROUP
FROM
  SQL_TABL; ORDE PR
---- --
2005 50
2005 25
2005 24

The above is a good start, but as you will likely note with a larger data set, you have repeating rows. Now, we use GROUP BY to produce a distinct combination of ORDER_YEAR and PRODUCT_GROUP: SELECT
  TO_CHAR(ORD_DATE,'YYYY') ORDER_YEAR,
  SUBSTR(PROD_CODE,3,2) PRODUCT_GROUP
FROM
  SQL_TABL
GROUP BY
  TO_CHAR(ORD_DATE,'YYYY'),
  SUBSTR(PROD_CODE,3,2); ORDE PR
---- --
2005 24
2005 25
2005 50

Now, we add in your calculation for the sales, enclosed in a SUM( ) function so that it adds the values from the various rows: SELECT
  TO_CHAR(ORD_DATE,'YYYY') ORDER_YEAR,
  SUBSTR(PROD_CODE,3,2) PRODUCT_GROUP,
  SUM(QTY*UNIT_PRICE+SHIP_COST) TOTAL_SALES FROM
  SQL_TABL
GROUP BY
  TO_CHAR(ORD_DATE,'YYYY'),
  SUBSTR(PROD_CODE,3,2); ORDE PR TOTAL_SALES
---- -- -----------

2005 24     2591.88
2005 25     3731.29
2005 50      787.43

The order of the rows returned may change from what is desired, so it may be necessary to add an ORDER BY clause after the GROUP BY clause: ORDER BY
  TO_CHAR(ORD_DATE,'YYYY'),
  SUBSTR(PROD_CODE,3,2) Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Dec 02 2007 - 15:14:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US