OraFAQ Forum: SQL & PL/SQL » sum when reference in another table

Home » SQL & PL/SQL » SQL & PL/SQL » sum when reference in another table (Oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sum when reference in another table [message #536169]

Mon, 19 December 2011 11:35

mecctro
Messages: 29
Registered: December 2011

Junior Member

I have a following problem.

Two tables: article i mutations

Article:


artno       descr       qty      sales
1           beer          1        5
2           coke          1        7
3           wine          1        4
4           beer  ct      12       2
5           coke  ct      6        3
6           wine ct       12       2
7           beer pl      336       1
8           coke pl      336       0

and mutations:

artno       mutation
1             4
1             7
2             5
2             8
3             6

I want to get the result like


article        sales
beer             365                 '5+2*12+1*336
coke              25                 '7+3*6
wine              28                 ' 4+2*12

How to do a query.

Report message to a moderator

Re: sum when reference in another table [message #536171 is a reply to message #536169]

Mon, 19 December 2011 11:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What changed your mind about this forum?

mecctro wrote on Fri, 02 December 2011 04:26

Quote:
So basically your company does not want you to query your company's database. Then don't query it!

Back from work.

Hundreds of people decided not to give me code and I'm OK with that. They maybe leared it hard way, dont want to give it to some lazy sob and I respect that. But all but one decided not to give it to me by not giving it, only you had to make all that philosophy. So you obviously have a problem.

I did solve my problem, but you'll have to seek help for your problem somwhere else. Anyway, to help you resolve at least a bit of your problem, I'll let you have the last word on this. Whatever you write, I will not reply. I'll just reply with OK so you know I read it and you can be happy.

Report message to a moderator

Re: sum when reference in another table [message #536174 is a reply to message #536171]

Mon, 19 December 2011 11:49

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In addition, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also always post your Oracle version, with 4 decimals.

Regard
Michel

Report message to a moderator

Re: sum when reference in another table [message #536175 is a reply to message #536174]

Mon, 19 December 2011 11:51

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Fri, 02 December 2011 13:33

For any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

If you do this, you will have fast and accurate answers.

Regards
Michel

[Updated on: Mon, 19 December 2011 11:51]

Report message to a moderator

Re: sum when reference in another table [message #536176 is a reply to message #536174]

Mon, 19 December 2011 11:54

mecctro
Messages: 29
Registered: December 2011

Junior Member

I don't have a test case 'cause I don't know where to start. I don't know how to get reference from another table and use it in the first one.

Report message to a moderator

Re: sum when reference in another table [message #536178 is a reply to message #536176]

Mon, 19 December 2011 12:02

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

Of course you have a test case - read our actual definition of test cases in Michels post. Then post it.

Report message to a moderator

Re: sum when reference in another table [message #536196 is a reply to message #536169]

Mon, 19 December 2011 13:35

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

You need to select the substring that constitutes the first word of the desc column, and the sum of the (qty column multiplied by the sales column) from both tables, joined such that the artno of the article table is equal to either the artno or mutation of the mutations table. Since you are summing, you will need to group by the columns that are not summed. You may need to multiply and join in one inner query, then sum and group by in an outer query.

If you would post create table and insert statements that produce the two tables of sample data that you provided, then any of us could easily take those and provide you with actual code instead of just a description of what code you should write. We do not ask for these things in order to be difficult. We ask for them so that we have what we need in order to help you. In your previous post and this one, if you had read and followed the forum guidelines and provided what is expected, you would have had a useful answer before your first reply. The longer that you wait to do this, the longer it will take to get help. If you never do it, then you may never get help.

Report message to a moderator

Re: sum when reference in another table [message #536232 is a reply to message #536196]

Mon, 19 December 2011 21:59

mecctro
Messages: 29
Registered: December 2011

Junior Member

Quote:

If you would post create table and insert statements that produce the two tables of sample data that you provided

Well, I can't because the tables are already there. Actual tables are more complicated, but the part I need looks like that. Anyway, I thought it would be a simple piece of code for you. If it isn't, don't bother, I'll do it via Excell. It's just so slow, so I tried to query it instead.

Quote:

equal to either the artno or mutation

It has to be equal to the artno in mutation table, but then how to get sales for mutation?

Thanks

Report message to a moderator

Re: sum when reference in another table [message #536233 is a reply to message #536232]

Mon, 19 December 2011 22:11

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

If you post the results of the following two queries, then it should provide the create table statements.

select dbms_metadata.get_ddl ('TABLE', 'ARTICLE') from dual;
select dbms_metadata.get_ddl ('TABLE', 'MUTATIONS') from dual;

Report message to a moderator

Re: sum when reference in another table [message #536234 is a reply to message #536232]

Mon, 19 December 2011 22:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I can't because the tables are already there.
Tables don't exist for us.
We can't write SQL without tables & data.

>I'll do it via Excell.
When your only tool is a hammer, every problem is viewed as being a nail.
While a hammer is a fine tool, it is a suboptimal choice when the task is to make 2 pieces of lumber from 1 board.
You'll never learn SQL, if you never use it.

[Updated on: Mon, 19 December 2011 22:18]

Report message to a moderator

Re: sum when reference in another table [message #536322 is a reply to message #536196]

Tue, 20 December 2011 05:17

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

Barbara Boehmer wrote on Mon, 19 December 2011 14:35

You need to select the substring that constitutes the first word of the desc column

Not sure what you mean but it is just plain join of mutations table to article table with group by and then join to article table again or join of mutations table to two instances of article table and then group by.

SY.

[Updated on: Tue, 20 December 2011 05:23]

Report message to a moderator

Re: sum when reference in another table [message #536390 is a reply to message #536322]

Tue, 20 December 2011 11:46

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

Solomon,

The following is what I had in mind. Please demonstrate what you have in mind. There are always various ways to do things.

-- create table and insert statements that the original poster failed to provide:

create table article
  (artno  number,
   descr  varchar2(15),
   qty    number,
   sales  number)
/
create table mutations
  (artno     number,
   mutation  number)
/
insert all
into article values (1,           'beer',          1,        5)
into article values (2,           'coke',          1,        7)
into article values (3,           'wine',          1,        4)
into article values (4,           'beer  ct',      12,       2)
into article values (5,           'coke  ct',      6,        3)
into article values (6,           'wine ct',       12,       2)
into article values (7,           'beer pl',      336,       1)
into article values (8,           'coke pl',      336,       0)
select * from dual
/
insert all
into mutations values (1,             4)
into mutations values (1,             7)
into mutations values (2,             5)
into mutations values (2,             8)
into mutations values (3,             6)
select * from dual
/

-- the query I had in mind:

SCOTT@orcl_11gR2> column article format a15
SCOTT@orcl_11gR2> select substr (descr, 1, instr (descr || ' ', ' ') - 1) as article,
  2  	    sum (qty * sales) as sales
  3  from   article
  4  where  article.artno in
  5  	    (select artno
  6  	     from   mutations
  7  	     union all
  8  	     select mutation
  9  	     from   mutations)
 10  group  by substr (descr, 1, instr (descr || ' ', ' ') - 1)
 11  order  by 1
 12  /

ARTICLE              SALES
--------------- ----------
beer                   365
coke                    25
wine                    28

3 rows selected.

Report message to a moderator

Re: sum when reference in another table [message #536393 is a reply to message #536390]

Tue, 20 December 2011 12:15

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Where do mutations come into the algorithm?

orcl> select substr (descr, 1, instr (descr || ' ', ' ') - 1) as article,sum (qty * sales) as sales
  2  from article
  3  group  by substr (descr, 1, instr (descr || ' ', ' ') - 1)
  4  order  by 1
  5  /

ARTICLE                                                           SALES
------------------------------------------------------------ ----------
beer                                                                365
coke                                                                 25
wine                                                                 28

orcl>

Report message to a moderator

Re: sum when reference in another table [message #536394 is a reply to message #536393]

Tue, 20 December 2011 12:17

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Oh, I see now - the mutations connect the related rows, I'm just relying on a naming convention. Silly of me.

Report message to a moderator

Re: sum when reference in another table [message #536395 is a reply to message #536393]

Tue, 20 December 2011 12:19

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

Good point, John. I think if you use the mutations, you can probably eliminate the need for substr. You need one or the other. My usage of both was unnecessary. It would probably be better to find some way to use the mutations in case the common substr of descr is more than one word.

Report message to a moderator

Re: sum when reference in another table [message #536396 is a reply to message #536390]

Tue, 20 December 2011 12:30

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

Barbara Boehmer wrote on Tue, 20 December 2011 12:46

Please demonstrate what you have in mind.

Here it goes:

select  a1.descr,
        sum(a1.qty * a1.sales) / count(*) + sum(a2.qty * a2.sales) sales
  from  mutations m,
        article a1,
        article a2
  where m.artno = a1.artno
    and m.mutation = a2.artno
  group by a1.artno,
           a1.descr
/

DESCR                SALES
--------------- ----------
coke                    25
wine                    28
beer                   365

SQL>

Or:

select  a.descr,
        a.qty * a.sales + m.sales sales
  from  article a,
        (
         select  m.artno,
                 sum(a.qty * a.sales) sales
           from  mutations m,
                 article a
           where m.mutation = a.artno
           group by m.artno
        ) m
  where m.artno = a.artno
/

DESCR                SALES
--------------- ----------
beer                   365
coke                    25
wine                    28

SQL>

SY.

Report message to a moderator

Re: sum when reference in another table [message #536397 is a reply to message #536396]

Tue, 20 December 2011 12:33

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

Here is another one that avoids the substr:

SCOTT@orcl_11gR2> column article format a15
SCOTT@orcl_11gR2> select descr as article,
  2  	    sum (qty * sales) as sales
  3  from   (select a.descr, a.qty, a.sales
  4  	     from   article a, mutations m
  5  	     where  a.artno = m.artno
  6  	     union
  7  	     select a2.descr, a.qty, a.sales
  8  	     from   article a, mutations m, article a2
  9  	     where  a.artno = m.mutation
 10  	     and    m.artno = a2.artno)
 11  group  by descr
 12  order  by 1
 13  /

ARTICLE              SALES
--------------- ----------
beer                   365
coke                    25
wine                    28

3 rows selected.

Report message to a moderator

Re: sum when reference in another table [message #536398 is a reply to message #536397]

Tue, 20 December 2011 12:38

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

It looks like, in order to avoid using substr and avoid duplicate rows, you have to either divide by count(*) as in Solomon's first example or add the two values as in Solomon's second example or use union (or distinct) to get distinct values as in my second example.

Report message to a moderator

Re: sum when reference in another table [message #536399 is a reply to message #536398]

Tue, 20 December 2011 12:46

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Three solutions, but even though the SQL is equivalent, see which is best once Oracle optimizer looks at it:

orcl> set autot traceonly exp
orcl> select  a1.descr,
  2          sum(a1.qty * a1.sales) / count(*) + sum(a2.qty * a2.sales) sales
  3    from  mutations m,
  4          article a1,
  5          article a2
  6    where m.artno = a1.artno
  7      and m.mutation = a2.artno
  8    group by a1.artno,
  9             a1.descr
 10  /

Execution Plan
----------------------------------------------------------
Plan hash value: 668415253

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     5 |   565 |    11  (19)| 00:00:01 |
|   1 |  HASH GROUP BY               |           |     5 |   565 |    11  (19)| 00:00:01 |
|*  2 |   HASH JOIN                  |           |     5 |   565 |    10  (10)| 00:00:01 |
|*  3 |    HASH JOIN                 |           |     5 |   370 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS STORAGE FULL| MUTATIONS |     5 |   130 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS STORAGE FULL| ARTICLE   |     8 |   384 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS STORAGE FULL | ARTICLE   |     8 |   312 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("M"."MUTATION"="A2"."ARTNO")
   3 - access("M"."ARTNO"="A1"."ARTNO")

Note
-----
   - dynamic sampling used for this statement (level=2)

orcl> select  a.descr,
  2          a.qty * a.sales + m.sales sales
  3    from  article a,
  4          (
  5           select  m.artno,
  6                   sum(a.qty * a.sales) sales
  7             from  mutations m,
  8                   article a
  9             where m.mutation = a.artno
 10             group by m.artno
 11          ) m
 12    where m.artno = a.artno
 13  /

Execution Plan
----------------------------------------------------------
Plan hash value: 67613613

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     5 |   370 |    11  (19)| 00:00:01 |
|*  1 |  HASH JOIN                    |           |     5 |   370 |    11  (19)| 00:00:01 |
|   2 |   VIEW                        |           |     5 |   130 |     8  (25)| 00:00:01 |
|   3 |    HASH GROUP BY              |           |     5 |   325 |     8  (25)| 00:00:01 |
|*  4 |     HASH JOIN                 |           |     5 |   325 |     7  (15)| 00:00:01 |
|   5 |      TABLE ACCESS STORAGE FULL| MUTATIONS |     5 |   130 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS STORAGE FULL| ARTICLE   |     8 |   312 |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS STORAGE FULL   | ARTICLE   |     8 |   384 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("M"."ARTNO"="A"."ARTNO")
   4 - access("M"."MUTATION"="A"."ARTNO")

Note
-----
   - dynamic sampling used for this statement (level=2)

orcl> select descr as article,
  2                 sum (qty * sales) as sales
  3      from   (select a.descr, a.qty, a.sales
  4                  from   article a, mutations m
  5                  where  a.artno = m.artno
  6                  union
  7                  select a2.descr, a.qty, a.sales
  8                  from   article a, mutations m, article a2
  9                  where  a.artno = m.mutation
 10                  and    m.artno = a2.artno)
 11     group  by descr
 12     order  by 1
 13  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3814451275

---------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |           |    10 |   350 |    20  (25)| 00:00:01 |
|   1 |  SORT GROUP BY                  |           |    10 |   350 |    20  (25)| 00:00:01 |
|   2 |   VIEW                          |           |    10 |   350 |    19  (22)| 00:00:01 |
|   3 |    SORT UNIQUE                  |           |    10 |   740 |    19  (69)| 00:00:01 |
|   4 |     UNION-ALL                   |           |       |       |            |          |
|*  5 |      HASH JOIN                  |           |     5 |   305 |     7  (15)| 00:00:01 |
|   6 |       TABLE ACCESS STORAGE FULL | MUTATIONS |     5 |    65 |     3   (0)| 00:00:01 |
|   7 |       TABLE ACCESS STORAGE FULL | ARTICLE   |     8 |   384 |     3   (0)| 00:00:01 |
|*  8 |      HASH JOIN                  |           |     5 |   435 |    10  (10)| 00:00:01 |
|*  9 |       HASH JOIN                 |           |     5 |   325 |     7  (15)| 00:00:01 |
|  10 |        TABLE ACCESS STORAGE FULL| MUTATIONS |     5 |   130 |     3   (0)| 00:00:01 |
|  11 |        TABLE ACCESS STORAGE FULL| ARTICLE   |     8 |   312 |     3   (0)| 00:00:01 |
|  12 |       TABLE ACCESS STORAGE FULL | ARTICLE   |     8 |   176 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"."ARTNO"="M"."ARTNO")
   8 - access("M"."ARTNO"="A2"."ARTNO")
   9 - access("A"."ARTNO"="M"."MUTATION")

Note
-----
   - dynamic sampling used for this statement (level=2)

orcl>

Over to you, mecctro.

Report message to a moderator

Re: sum when reference in another table [message #536400 is a reply to message #536399]

Tue, 20 December 2011 12:50

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

It looks like Solomon's first query, without any sub-queries is the winner.

Report message to a moderator

Re: sum when reference in another table [message #536401 is a reply to message #536400]

Tue, 20 December 2011 12:56

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

Actually it can be simplified:

select  a1.descr,
        a1.qty * a1.sales + sum(a2.qty * a2.sales) sales
  from  mutations m,
        article a1,
        article a2
  where m.artno = a1.artno
    and m.mutation = a2.artno
  group by a1.artno,
           a1.descr,
           a1.qty,
           a1.sales
/

DESCR                SALES
--------------- ----------
beer                   365
coke                    25
wine                    28

SQL>

SY.

Report message to a moderator

Re: sum when reference in another table [message #536402 is a reply to message #536400]

Tue, 20 December 2011 13:03

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

That's what I think. The second query scans mutations (step id 5) then article (6) to do the join on mutation-artno, then groups to produce a view (2). Only then can it scan article again (7) to do that last join of artno-artno.
The first query scans mutations (4) then article (5) to do the artno-artno join, then scans article (6) to do the join on mutation-artno (2). The grouping (1) can happen concurrently with (2).
At least, I think that is what happens.

Report message to a moderator

Re: sum when reference in another table [message #536419 is a reply to message #536402]

Tue, 20 December 2011 21:52

mecctro
Messages: 29
Registered: December 2011

Junior Member

Thanks, have to go to work now, so I'll try the codes there.

Report message to a moderator

Re: sum when reference in another table [message #536564 is a reply to message #536419]

Wed, 21 December 2011 09:49

mecctro
Messages: 29
Registered: December 2011

Junior Member

I tried these:

select  a1.descr,
        sum(a1.qty * a1.sales) / count(*) + sum(a2.qty * a2.sales) sales
  from  mutations m,
        article a1,
        article a2
  where m.artno = a1.artno
    and m.mutation = a2.artno
  group by a1.artno,
           a1.descr
/

DESCR                SALES
--------------- ----------
coke                    25
wine                    28
beer                   365

SQL>

select  a.descr,
        a.qty * a.sales + m.sales sales
  from  article a,
        (
         select  m.artno,
                 sum(a.qty * a.sales) sales
           from  mutations m,
                 article a
           where m.mutation = a.artno
           group by m.artno
        ) m
  where m.artno = a.artno
/

DESCR                SALES
--------------- ----------
beer                   365
coke                    25
wine                    28

SQL>

and it says ORA-00942: table or view does not exist

I found this on the net: 'Existing user tables and views can be listed by querying the data dictionary.' I know how to query tables but don't know about view. Also, how can I find out which version of Oracle we have. I'm accessing it via VB in Excell.

Report message to a moderator

Re: sum when reference in another table [message #536565 is a reply to message #536564]

Wed, 21 December 2011 09:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Also, how can I find out which version of Oracle we have.

SELECT * FROM V$VERSION;

Report message to a moderator

Re: sum when reference in another table [message #536568 is a reply to message #536565]

Wed, 21 December 2011 10:11

mecctro
Messages: 29
Registered: December 2011

Junior Member

Thanks, will find out tomorrow.

Report message to a moderator

Re: sum when reference in another table [message #536571 is a reply to message #536564]

Wed, 21 December 2011 10:19

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

mecctro wrote on Wed, 21 December 2011 15:49

I know how to query tables but don't know about view.

user_views and all_views

Report message to a moderator

Re: sum when reference in another table [message #536726 is a reply to message #536571]

Thu, 22 December 2011 06:45

mecctro
Messages: 29
Registered: December 2011

Junior Member

So, the version is 9i 92080.

I did something wrong, actually, the first query works

select  a1.descr,
        sum(a1.qty * a1.sales) / count(*) + sum(a2.qty * a2.sales) sales
  from  mutations m,
        article a1,
        article a2
  where m.artno = a1.artno
    and m.mutation = a2.artno
  group by a1.artno,
           a1.descr

I probably wrote something wrong the first time. But I have another problem: not all the articles from the article table have a mutation pair. Haven't been thinkiing about it before. So this works only for articles which are in both tables. Any solutions (with this code, because others don't work).

Report message to a moderator

Re: sum when reference in another table [message #536731 is a reply to message #536726]

Thu, 22 December 2011 06:53

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

Use outer-joins, and nvls on the a2 columns in the sum.

Report message to a moderator

Re: sum when reference in another table [message #536767 is a reply to message #536731]

Thu, 22 December 2011 14:26

mecctro
Messages: 29
Registered: December 2011

Junior Member

Easier said than done, I'm not very good with sql.

Report message to a moderator

Re: sum when reference in another table [message #536768 is a reply to message #536767]

Thu, 22 December 2011 14:29

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

select  a.descr,
        a.qty * a.sales + nvl(m.sales,0) sales
  from  article a,
        (
         select  m.artno,
                 sum(a.qty * a.sales) sales
           from  mutations m,
                 article a
           where m.mutation = a.artno
           group by m.artno
        ) m
  where m.artno(+) = a.artno
/

DESCR                SALES
--------------- ----------
beer                   365
coke                    25
wine                    28
coke  ct                18
coke pl                  0
wine ct                 24
beer pl                336
beer  ct                24

8 rows selected.

SQL>

SY.

Report message to a moderator

Re: sum when reference in another table [message #536770 is a reply to message #536768]

Thu, 22 December 2011 15:13

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

In the example below, I added lemonade to the article table, but no corresponding pair in the mutations table. It should work in 9i, so if it doesn't then you are doing something wrong. I added just the code at the bottom, so that you can copy and paste it, to eliminate typing errors.

-- test data:

SCOTT@orcl_11gR2> select * from article
  2  /

     ARTNO DESCR                  QTY      SALES
---------- --------------- ---------- ----------
         1 beer                     1          5
         2 coke                     1          7
         3 wine                     1          4
         4 beer  ct                12          2
         5 coke  ct                 6          3
         6 wine ct                 12          2
         7 beer pl                336          1
         8 coke pl                336          0
         9 lemonade                 2          3

9 rows selected.

SCOTT@orcl_11gR2> select * from mutations
  2  /

     ARTNO   MUTATION
---------- ----------
         1          4
         1          7
         2          5
         2          8
         3          6

5 rows selected.

-- query:

SCOTT@orcl_11gR2> column article format a15
SCOTT@orcl_11gR2> select nvl (a2.descr, a.descr) as article,
  2  	    sum (a.qty * a.sales) as sales
  3  from   article a, mutations m, article a2
  4  where  a.artno = m.mutation (+)
  5  and    m.artno = a2.artno (+)
  6  group  by nvl (a2.descr, a.descr)
  7  order  by article
  8  /

ARTICLE              SALES
--------------- ----------
beer                   365
coke                    25
lemonade                 6
wine                    28

4 rows selected.

-- code you can copy, by clicking on select all, and paste:

select nvl (a2.descr, a.descr) as article, 
       sum (a.qty * a.sales) as sales
from   article a, mutations m, article a2
where  a.artno = m.mutation (+)
and    m.artno = a2.artno (+)
group  by nvl (a2.descr, a.descr)
order  by article
/

[Updated on: Thu, 22 December 2011 15:34]

Report message to a moderator

Re: sum when reference in another table [message #536772 is a reply to message #536770]

Thu, 22 December 2011 15:53

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

Barabara,

There is no need to join article table twice if OP doesn't want to display articles already counted in through mutations:

select  max(nvl2(m.artno,null,a.descr)) descr,
        sum(qty * sales) sales
  from  article a,
        mutations m
  where a.artno = m.mutation (+)
  group by nvl(m.artno,a.artno)
/

DESCR                SALES
--------------- ----------
beer                   365
coke                    25
wine                    28
lemonade                 6

SQL>

SY.

Report message to a moderator

Re: sum when reference in another table [message #536896 is a reply to message #536772]

Fri, 23 December 2011 14:30

mecctro
Messages: 29
Registered: December 2011

Junior Member

Quote:

so that you can copy and paste it, to eliminate typing errors.

It still won't work:

http://i44.tinypic.com/168aedu.jpg

http://i40.tinypic.com/eb4rbq.jpg

[mod-edit: links fixed by bb]

[Updated on: Fri, 23 December 2011 14:56] by Moderator

Report message to a moderator

Re: sum when reference in another table [message #536897 is a reply to message #536896]

Fri, 23 December 2011 14:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: sum when reference in another table [message #536899 is a reply to message #536896]

Fri, 23 December 2011 15:07

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

As previously stated, you need to test it from SQL*Plus and copy and paste your SQL*Plus session. You should get it working properly in SQL*Plus before you try to use it from something else. Why are you so stubbornly refusing to supply the things that enable us to help you? Do you realize that we cannot properly diagnose the problem without these things? Do you understand that what you are using is not SQL*Plus? Do you know how to use SQL*Plus?

You are still getting the table does not exist error. This could be caused by a number of things. It could be that it is thinking something other than a table should be a table. It could be that your table does not exist. It could be that the table exists in another schema. It could be that you do not have privileges to view the table. It could be that the table has been created in upper or lower or mixed case and what you are using does not match. It could be that what you are using instead of SQL*Plus is not compatible with your Oracle version.

Can you at least post a copy and paste of the results, from SQL*Plus of:

DESCRIBE article
DESCRIBE mutations

so that we can see what the exact names, including case and structure are? These are standard things that every forum asks for and anyone who wants help complies with. We are not making unreasonable requests. If you are unwilling to comply with the forum guidelines, then please do not bother us with your problems.

Report message to a moderator

Re: sum when reference in another table [message #536906 is a reply to message #536899]

Fri, 23 December 2011 16:20

mecctro
Messages: 29
Registered: December 2011

Junior Member

I don't think your requests are unreasonable, I just can't meet them. This is my company's database so I have what I have. I can't use SQL*plus or anything. Normally, we use some 10 years old application which is not very useful. Once, I found an Excell table with some query so I use it now (just changing queries). For example with our application I can get sales for basic item and for it's mutation pair so I have to calculate it myself which is not easy for over 3000 items and some pervers packages with 14 or 17 units. I can't copy/paste from application so I have to use some queries that 10 years ago somebody thought I might need. I'm sorry to bother you and I don't think you're obliged to help me. If you can, great, if not, not.

However, this code was working

select  a1.descr,
        sum(a1.qty * a1.sales) / count(*) + sum(a2.qty * a2.sales) sales
  from  mutations m,
        article a1,
        article a2
  where m.artno = a1.artno
    and m.mutation = a2.artno
  group by a1.artno,
           a1.descr

but it doesn't cover items without mutation pair.

Thanks for your trouble.

Report message to a moderator

Re: sum when reference in another table [message #536909 is a reply to message #536906]

Fri, 23 December 2011 18:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If I were in your shoes, I'd download & install Oracle on my PC,
so I would have access to SQL*Plus, SQL*Loader, etc.
Then they could be used to access the Production DB.

Report message to a moderator

Re: sum when reference in another table [message #536912 is a reply to message #536909]

Fri, 23 December 2011 22:28

mecctro
Messages: 29
Registered: December 2011

Junior Member

I can't install because I don't have administrator privileges. Also, I don't want to use something they didn't allow me. I can get the job done with Excell (I query all the mutation pairs and then the spreadsheet uses those I need), but it would be neat to do it with query, because this way, an extra sheet is needed. So, I just asked here thinking that it would be a simple piece of code. If it isn't, it's OK, I really appreciate your (all of you) effort to help me, and I thank you.

Report message to a moderator

Re: sum when reference in another table [message #536970 is a reply to message #536912]

Sun, 25 December 2011 02:47

mecctro
Messages: 29
Registered: December 2011

Junior Member

I apologize for my silliness, but both codes work. Since I wrote this from memory, I missed actual table name. It's mutation, not mutations. However, another problem occurred wit both these codes:

select nvl (a2.descr, a.descr) as article, 
       sum (a.qty * a.sales) as sales
from   article a, mutations m, article a2
where  a.artno = m.mutation (+)
and    m.artno = a2.artno (+)
group  by nvl (a2.descr, a.descr)
order  by article
/

select  max(nvl2(m.artno,null,a.descr)) descr,
        sum(qty * sales) sales
  from  article a,
        mutations m
  where a.artno = m.mutation (+)
  group by nvl(m.artno,a.artno)
/

DESCR                SALES
--------------- ----------
beer                   365
coke                    25
wine                    28
lemonade                 6

SQL>

Since some items' base unit isn't 1, (sometimes we sell beer by 6-pack and 24 pack, so the base unit is 6).

I tried this:

sum (a.qty * a.sales)/a.qty

but then it doesn't work for items with more mutation pairs (it separates each pair in new line).

Then I tried this code and it works OK for couple of items, but when I ran it at 100 items I didn't get any answer for 10 minutes so I gave up.

  select a1.artno, (a1.sales*a1.qty + sum(case when a1.artno = m.artno and a2.artno = m.mutation then
        a2.sales*a2.qty else 0 end))/ a1.qty 
        from article a1, article a2, mutation m 
        group by a1.artno, a1.qty, a1.sales*a1.qty"

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	How to Insert all user names into a table from sql
Next Topic:	Oracle PL/SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 08 08:29:35 CST 2025