CODEWITHSUNDEEP
mysqlsql
EdwinU
EdwinU

Reputation: 85

how to get data from another table without joining in mysql

I would like to get the name of the product, but keeping the same amount of sales, when joining, I only get 1 record, but I want to get the 3 sales records

Products

this is my sales chart Sales

enter image description here

my sql query

SELECT p.name as 'Descripción', p.cost as 'Precio de costo',p.price as 'Precio de venta',
sum((p.price-ps.discount)+(ps.discount/ps.qty)) as 'precio con descuento', ps.discount as 'descuento', ps.qty as 'cantidad',
ps.total as 'Sub total venta', (sum((p.price-ps.discount)+(ps.discount/ps.qty))-p.cost)/ps.qty as 'Utilidad',
sum((p.price-ps.discount)+(ps.discount/ps.qty))-p.cost as 'Sub Total Utilidad'
FROM product_sales ps
inner join products p on p.id = ps.product_id

This is the result I wish to obtain, 3 records instead of 1.

enter image description here

Upvotes: 0

Views: 1163

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

Your query would be invalid in most databases and in the most recent version of MySQL. Why? It is missing the GROUP BY. This is an aggregation query because you have SUM(), but I don't think it is necessary.

I would advise you to simply fix your query:

SELECT p.name as Descripción, p.cost as `Precio de costo`,
       p.price as `Precio de venta`,
       ((p.price-ps.discount)+(ps.discount/ps.qty)) as `precio con descuento`,
       ps.discount as `descuento`, ps.qty as 'cantidad',
       ps.total as `Sub total venta`,
       ((p.price-ps.discount)+(ps.discount/ps.qty))-p.cost)/ps.qty as Utilidad,
       ((p.price-ps.discount)+(ps.discount/ps.qty))-p.cost as `Sub Total Utilidad`
from product_sales p join
     products p
     on p.id = ps.product_id ;

Upvotes: 1

forpas
forpas

Reputation: 164154

You don't need the aggregation function SUM() since you are not aggregating over the rows of the results.
Also you must calculate the column Sub total venta correctly:

SELECT 
  p.name as `Descripción`, 
  p.cost as `Precio de costo`,
  p.price as `Precio de venta`,
  (p.price-ps.discount)+(ps.discount/ps.qty) as `precio con descuento`, 
  ps.discount as `descuento`, 
  ps.qty as `cantidad`,
  p.price * ps.qty - ps.discount as `Sub total venta`, 
  (((p.price-ps.discount)+(ps.discount/ps.qty))-p.cost)/ps.qty as `Utilidad`,
  (p.price-ps.discount)+(ps.discount/ps.qty)-p.cost as `Sub Total Utilidad`
FROM product_sales ps INNER JOIN products p 
ON p.id = ps.product_id

Upvotes: 1

Deepak Chahar
Deepak Chahar

Reputation: 3

CREATE VIEW in MYSQL and use it again and again with the simple SELECT query

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name JOIN table_name ON condition WHERE condition;

Reference link of MySQL CREATE VIEW

Upvotes: 0

Pan
Pan

Reputation: 341

Remove all occurrences of SUM from your statement. This is an aggregation function which will sum the values of all rows and return one row.

columnA + columnB will give you the sum of two columns in the same row.

Upvotes: 0

Related Questions