Select with Inner or Outer joins

Question

0.00/5 (No votes)

See more:

Hello,
I have a table of articles:

VB

ID_ART CODE_ART
1       AAA
2       BBB
3       CCC
4       DDD
5       EEE

And a Table Mouvements:

VB

ID_SOR	ID_ART	QUANTITY_SOR	PRICE_SOR	QUANTITY_SOR * PRICE_SOR
1	1	1	           25.23	25.23
2	1	5	           22.51	112.55
3	2	1	              15	15
4	4	8	               5	40
5	4	2	             5.6	11.2
6	4	4	             5.3	21.2
7	2	2	            14.5	29

I would like to have a table wich do the sum(QUANTITY_SOR * PRICE_SOR) grouping by ID_ART and must be with the CODE_A from the table Articles:

VB

ID_ART	CODE_ART	SUM(QUANTITY_SOR * PRICE_SOR)
1	     AAA	137.78
2	     BBB	44
4	     DDD	72.4

I tried with several kinds of select statements without result.
Best Regrads.

Posted 5-Oct-13 11:23am

nabilg

Add a Solution

3 solutions

Solution 1

Hi

Here is solution to your problem

select p.code_art ,sum(s.Quantity_sor * s.Price_sor ) as total
from articles s join Mouvements p on s.ID_ART=p.ID_ART
group by p.code_art

Do not forget to mark this as answer ,if it solves your problem

Regards
Mubin

Posted 5-Oct-13 19:43pm

Mubin M. Shaikh

Solution 3

Select Table1.ID_ART,
Code_Art,
SUM(QUANTITY_SORPRICE_SOR) AS 'SUM(QUANTITY_SOR *PRICE_SOR)
From Table1
inner join Table2
ON Tabe1.ID_ART = Table2.ID_ART
Group By Table1.ID_ART,CODE_ART

Posted 5-Oct-13 22:22pm

VeeraAnnapureddy

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Azee** · Accepted Answer · 2013-10-05T21:39:00

Solution 2

Hey there,

I replicated your scenario and this will get you your result.

SQL

SELECT a.ID_ART, a.CODE_ART, SUM(m.QUANTITY_SOR * m.PRICE_SOR) FROM mouvements m
inner join articles a on a.ID_ART = m.ID_ART
group by a.ID_ART, a.CODE_ART

Cheers.

Azee...

Posted 5-Oct-13 21:39pm

Azee