Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • 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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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