How get earnings per month MYSQL Query

Question

I have two tables, one table called "tbl_materiales", and another called "tbl_pedidos".. In the table called tbl_materiales" I have information about all my products, Like Description, and the most important "Price"...

In my table "tbl_pedidos", i register all information of products that the user register in the website.

For example:

tbl_materiales:

IdProduct     Description   Price
   5        Product one     8
   6        Product three   10
   7        Product four    15

tbl_pedidos

IdProduct   Quantity    Month 
   5        10          January
   6        5           January
   7        2           February

So, I want to know all the earnings PER month... I want to have this: The column earnings is the multiplication of tbl_pedidos.Quantity * tbl_materiales.Price, obviously it depends of the price of the product, and the quantity sold out.

Month       Earnings
January          130
February          30

Now, I have this, but it doesn't bring me the correct information...

SELECT tbl_pedidos.Mes
     , (SUM(tbl_pedidos.Cantidad) * tbl_materiales.Precio) as Total 
FROM tbl_pedidos 
INNER JOIN tbl_materiales 
  ON tbl_pedidos.IdMaterial = tbl_materiales.IdMaterial 
GROUP BY tbl_pedidos.Mes 
ORDER BY tbl_pedidos.Fecha;

I think the ()'s are just wrong on your sum `, (SUM(tbl_pedidos.Cantidad) * tbl_materiales.Precio) as Total` should be , `SUM(tbl_pedidos.Cantidad * tbl_materiales.Precio) as Total` I think you want the sum of price times quantity for each ID product and then allow the grouping to sum by month. Also possibly at fault IDPRoduct in your table definition/examples but IDMaterial in the SQL. — xQbert, May 15 '17 at 20:26

score 0 · Answer 1 · edited May 15 '17 at 22:00

0

The query can be like :

SELECT tbl_p.Month
     ,sum(as tbl_m.Price*TP.Quantity) AS Earnings 
FROM tbl_materiales AS tbl_m 
JOIN tbl_pedidos AS tbl_p
  ON tbl_m.IdProduct = tbl_p.IdProduct
 GROUP 
    BY tbl_p.Month;

edited May 15 '17 at 22:00

Strawberry

33,750
13
40
57

answered May 15 '17 at 20:38

Rajadip

2,799
2
11
15

score 0 · Answer 2 · answered May 15 '17 at 20:40

SELECT tbl_pedidos.Mes , SUM(tbl_pedidos.Cantidad*tbl_materiales.Precio) as Total 
FROM tbl_pedidos 
INNER JOIN tbl_materiales 
  ON tbl_pedidos.IdMaterial = tbl_materiales.IdMaterial 
GROUP BY tbl_pedidos.Mes 
ORDER BY tbl_pedidos.Fecha;

Check http://sqlfiddle.com/#!9/de665b/1

score -1 · Answer 3 · edited May 15 '17 at 20:30

-1

In this case I have used Where instead of Join, maybe de next sentence resolve your problem:

select TP.Month,sum(TM.Price*TP.Quantity) as Earnings 
from TBL_Pedidos TBP,TBL_Materiales TM 
where TP.IdProduct = TM.Id_Product 
group by TP.Month

Group by is the solution

edited May 15 '17 at 20:30

xQbert

34,733
2
41
62

answered May 15 '17 at 20:29

juan carlos gonzalez aguilar

1

1

Using comma's in the from clause was the ANSI standard before 1992 (standard in 89-part of 92 I believe. I think in the past 25 years we've started using INNER join syntax and should press people to utilize the current standard. http://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89. Aside from that,what MAY help this query is ()'s use which you don't mention you changed! – xQbert May 15 '17 at 20:30

How get earnings per month MYSQL Query

3 Answers3