CODEWITHSUNDEEP
phpmysql
Diomedes Andreou
Diomedes Andreou

Reputation: 385

Get same rows within a table

Ok i have table with this structure:

order_id product_id price

18645 289 5.90

18644 219 1.80

18644 109 2.30

18643 289 4.50

18642 180 9.80

18642 190 3.40

How i'm able to count same product_id's from a range of order_id's along with their according total prices per group of product_ids ?

Upvotes: 0

Views: 64

Answers (3)

Omesh
Omesh

Reputation: 29091

to get the product wise COUNT and SUM you need to use aggregate functions GROUP BY product_id:

SELECT product_id,
       COUNT(1) AS product_count,
       SUM(price) AS total_price
FROM table_name
WHERE order_id BETWEEN 18642  AND 18645
GROUP BY product_id
ORDER BY product_count ASC, total_price ASC

Upvotes: 2

John Woo
John Woo

Reputation: 263723

Try

SELECT PRODUCT_ID,
       COUNT(product_id) totalCount,
       SUM(price) totalPrice
FROM tableName
WHERE Order_ID BETWEEN 00001  AND 99999
GROUP BY PRODUCT_ID

Upvotes: 2

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

You need to just group by product_id

select product_id,
       count(*) as count ,
       sum(price) as price
from   <table>
where  order_id beteen <val1> and <val2>
group by product_id

Upvotes: 0

Related Questions