• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

MYSQL - Question Query

hi,
I have two tables, one containing sales data and other data of purchases, I have to query this table and sort data, similar to a SELL OUT, ¿¿the tables are as follows:

TABLE_NAME = VENDUTI

FIELDS:

IDARTICOLO: INTEGER
QUANTITA: INTEGER

IDARTICOLO---QUANTITA

       1                             1
       45                           1
       23                           2
       1                             12
       54                           4
       1                             7


TABLE_NAME = ACQUISTI

FIELDS:

IDARTICOLO: INTEGER
QUANTITA: INTEGER


IDARTICOLO---QUANTITA

       1                             3
       45                           2
       23                           23
       1                             43
       54                           24
       1                             27


the query must show:

Sellout is not a field in the table, if necessary I can create, the important thing is that this formula contains

V = VENDUTI.QUANTITA
A = ACQUISTI.QUANTITA

Sellout = V * 100 / A

data must also be grouped
0
danz67
Asked:
danz67
  • 3
  • 2
1 Solution
 
danz67Author Commented:
the query must show:

IDARTICOLO, QUANTITA_VENDUTA, QUANTITA_ACQUISTATA, SELLOUT
0
 
_agx_Commented:
Can you post an example of the desired results?
0
 
ralmadaCommented:
something like this?
select 	V.IDARTICOLO, 
	V.QUANTITA_VENDUTA, 
	A.QUANTITA_ACQUISTATA, 
	V.QUANTITA_VENDUTA * 100 / A.QUANTITA_ACQUISTATA as Sellout	
from (
	select 	idarticolo,
		sum(quantita) as quantita_venduta
	from	Venduti 
	group by idarticolo
) V
inner join (
	select 	idarticolo,
		sum(quantita) as QUANTITA_ACQUISTATA
	from	Acquisti 
	group by idarticolo
) A on V.IDARTICOLO = A.IDARTICOLO

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
danz67Author Commented:
ralmada:

ok, everything works perfectly, I would need to add the Description field and grouping by COLOR and SIZE fields
0
 
ralmadaCommented:
Something like this:
select 	V.IDARTICOLO, 
	V.Description,
	V.Color,
	V.Size,
	V.QUANTITA_VENDUTA, 
	A.QUANTITA_ACQUISTATA, 
	V.QUANTITA_VENDUTA * 100 / A.QUANTITA_ACQUISTATA as Sellout	
from (
	select 	idarticolo,
		Description,
		Color,
		Size,
		sum(quantita) as quantita_venduta
	from	Venduti 
	group by idarticolo, Description, Color, Size

) V
inner join (
	select 	idarticolo,
		Description,
		Color,
		Size,
		sum(quantita) as QUANTITA_ACQUISTATA
	from	Acquisti 
	group by idarticolo, Description, Color, Size
) A on V.IDARTICOLO = A.IDARTICOLO

Open in new window

0
 
ralmadaCommented:
oops,
select 	V.IDARTICOLO, 
	V.Description,
	V.Color,
	V.Size,
	V.QUANTITA_VENDUTA, 
	A.QUANTITA_ACQUISTATA, 
	V.QUANTITA_VENDUTA * 100 / A.QUANTITA_ACQUISTATA as Sellout	
from (
	select 	idarticolo,
		Description,
		Color,
		Size,
		sum(quantita) as quantita_venduta
	from	Venduti 
	group by idarticolo, Description, Color, Size

) V
inner join (
	select 	idarticolo,
		Description,
		Color,
		Size,
		sum(quantita) as QUANTITA_ACQUISTATA
	from	Acquisti 
	group by idarticolo, Description, Color, Size
) A on V.IDARTICOLO = A.IDARTICOLO and A.Description = V.Description and A.Color = V.Color and A.Size = V.Size

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now