• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Ron McLeod
  • Tim Cooke
Sheriffs:
  • Devaka Cooray
  • paul wheaton
  • Mark Herschberg
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Frits Walraven
  • Jj Roberts
Bartenders:
  • Carey Brown
  • salvin francis
  • Piet Souris

Multiply columns in different tables

 
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The code should multiply price with quantity and group them by name. My code looks like this:

select k.knr,k.fnamn,k.enamn,(a.pris*o.antal)summa
from kund k,varugrupp v, kundorder ko, orderrad o, artikel a
where k.knr = ko.knr
and ko.ordnr = o.ordnr
and o.artnr = a.artnr
group by k.knr,k.fnamn,k.enamn
order by k.knr asc;

But i get error in the group by. Any suggestions?
 
Master Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What is the error?
 
Hugo Nilsson
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I get: ORA-00979: not a GROUP BY expression
 
Dave Tolls
Master Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, the columns in your query are not all in the GROUP BY.

Specifically that calculation at the end.

SHould that be a SUM?
eg SUM(a.pris*o.antal) summa
 
Hugo Nilsson
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No sum is not what im looking for . Then it shows to much. I just want the a.pris*o.antal
 
Marshal
Posts: 26377
81
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Hugo Nilsson wrote:No sum is not what im looking for . Then it shows to much. I just want the a.pris*o.antal



Then I'd say that "group by" is unnecessary, since you aren't actually using any grouping features. I'm still not clear on what that "summa" thing does though.
 
Dave Tolls
Master Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm not sure what each of those tables represents, but it looks like an order with order lines, each with an item being bought (artikel?)?
Something like that.

So each one of those things you are grouping by has numerous lines, which is why I thought you'd want the total for the order.

Except, you have a spurious table in there (varugrupp) that is not associated with anything, so every row in that table is being mapped to every row resulting from your WHERE clause, so maybe that's where your issue actually lies and SUM is the correct thing to use?
 
Marshal
Posts: 72048
312
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If pris means price and antal means count or quantity then OP does want to multiply.
 
Dave Tolls
Master Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
But the other columns returned are about the customer (number and name), so how does multiplying the single line items on every order make sense per customer unless you are adding them up as well?
Remove the group by and all you have is:

Knr     Sum
1        100
2        40
3        22
1        88

And so on.
Multiple lines with a cust id and a sum...even the title of that column implies "a total of what the customer has ordered".
 
Dave Tolls
Master Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
oops
 
Dave Tolls
Master Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
oops
 
Time flies like an arrow. Fruit flies like a banana. Steve flies like a tiny ad:
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic