MYSQL Query for SUM and DISTINCT?

Question

I have a database with the first five columns like this:

ID  NAME  QUANTITY   PRICE   KIND
1    Dog       2       5      A
2    Cat       1       6      B
3    Dog       2       5      C
4    Bird      5       5      C

(DOG QUANTITY and PRICE will always be the same)

What I want to do to is to something like

SELECT KIND, SUM(QUANTITY * PRICE) GROUP BY KIND WHERE DISTINCT NAME

So that I get something that looks like this:

A 10
B  6
C 25   

(The duplicate DOG is eliminated.)

I know my syntax above is grossly wrong -- it's just seems to be the most eloquent way of explaining what sort of thing I'm looking for.

In other words, I want to get rid of non-distinct NAMES then SUM the rest. I seem to be able to do one or the other but not both.

Any ideas? If worse comes to worst I can do it as a loop in PHP rather than as a single MYSQL query.

Answer 1

I'm not really clear about either what the rules are or why your table is in that format (with repeated name, quantity,price) but here is one way of getting your expected output.

select kind, SUM(quantity*price)
from
(
SELECT name, quantity, price, min(kind) kind
FROM YourTable
group by name, quantity, price
) t
group by kind

Answer 2

Here I chose the item with the lowest ID as the one to keep:

Select T.Kind, Sum( T.Quantity * T.Price ) As Total
From Table As T
Where Id = (
            Select Min(T2.Id)
            From Table As T2
            Where T2.Name = T.Name
            )
Group By T.Kind

Assuming that your table is unique on Name and Kind, you can do:

Select T.Kind, Sum( T.Quantity * T.Price ) As Total
From Table As T
Where T.Kind =  (
                Select Min(T2.Kind)
                From Table As T2
                Where T2.Name = T.Name
                )
Group By T.Kind