Is there a way to substitute GROUP BY

user_13025325 0
Viewed 100

I have two tables i'm working with: comporder(quantity,cod(Fk on cod(product),cod_ship); product(cod(Pk),price); I need to produce a query that will give me the sum of the prices of the products that are in the comporder table for each cod_ship; I came up with this query:

SELECT sum(p.price),c.cod_ship
FROM product as p JOIN comporder as c
ON(p.cod=c.cod)
GROUP BY c.cod_ship;

However I am not allowed to use the GROUP BY function and I can't seem to have the price where the quanity is above one. For exampe if in my comporder table I have:

  quantity     cod     cod_ship   
     2     "1234567890"   27       
     3     "1234567890"   28       
     2     "7894561230"   28       
     1     "5678324515"   28       
     4     "1234567890"   27       
     1     "1234567890"   27

And if in my product table I have:

     cod          price
 "1234567890"     20.00
 "7894561230"     19.99 
 "5678324515"     25.99

If I apply my query the result will be:

 sum     cod_ship
 60        27
 65.979    28

When te actual result should be, based on the quantity of the products in the table comporder:

sum    // cod_ship//
140      // 27//
125,97    //28//

So I can't seem to figure out how to get the sum also based on the quantity of the product and witouth the GROUP BY function, I should just show the sum as "output", can somebody help me out understand how can I do it?

REPLYING TO COMMENTS: I cannot use group by due to an assignment. I am using PostgreSQL 12.1

postgresql sql database
2 Answers

As requested by the OP in the comments here is a solution using GROUP BY:

SELECT SUM(price * quantity) as sum, cod_ship FROM comporders
INNER JOIN products ON products.cod = comporders.cod
GROUP BY cod_ship;

Edit:

Here is the solution without GROUP BY:

SELECT DISTINCT 
  (
    SELECT SUM(price * quantity)
       FROM products
    INNER JOIN comporders ON products.cod = comporders.cod
    WHERE cod_ship = results.cod_ship
  ) AS sum,
  cod_ship
FROM comporders AS results;

It works by first selecting a unique list of cod_ship ids (what we previously grouped the query by). Then we execute a subquery using the cod_ship id to calculate the sum for each column. We use the table alias results in order to reference the values in the parent query of the subquery.

SQL Fiddle Link

user_70903450

You can do aggregation in subselect like this:

SELECT (
    SELECT SUM(p.price) 
    FROM product AS p 
    WHERE p.cod = c.cod
) AS price, c.cod_ship
FROM comporder AS c
user_24282350
Related