CODEWITHSUNDEEP
mysql
Matt Elhotiby
Matt Elhotiby

Reputation: 44086

How to do a count query that groups by distinct product

Ok so i have this query 

SELECT *
FROM configure_system
WHERE EstimateID='EDB95PY0Z2'  AND StepID=1

which will return the following 8 rows...i need a query that only returns all the distinct productID and the total price and the quantity of them ...so for example

i want something like this

ProductID   ProductPrice TotalPrice quantity
2           575          1175       2
3           839          1678       2
9           1349         4047       3
12          1699         1699       2

I tried using the distinct query and count but have not been successful

Upvotes: 1

Views: 155

Answers (4)

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

It will be sth like this 

SELECT productId
      ,count(*)
      ,count(*)*price
  FROM [Table_1]
  GROUP BY productId,Price
  HAVING productId = 'blabla'

Upvotes: 1

Justin Wignall
Justin Wignall

Reputation: 3510

SELECT ProductID,ProductPrice,SUM(ProductPrice),COUNT(*)
FROM configure_system
WHERE EstimateID='EDB95PY0Z2'  AND StepID=1
GROUP BY ProductID,ProductPrice

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453908

SELECT   ProductID                            ,
         ProductPrice                         ,
         COUNT(*) * ProductPrice AS TotalPrice,
         COUNT(*)                AS quantity
FROM     configure_system
WHERE    EstimateID='EDB95PY0Z2'
AND      StepID    =1
GROUP BY ProductID,
         ProductPrice

Upvotes: 1

Tim
Tim

Reputation: 5421

You need to look at the "GROUP BY" clause, and "aggregate functions"

Upvotes: 1

Related Questions