Finding the SUM of values of a column of all records in sql 2005

I want to find the SUM of values in a column weight. I want this sum for all records that are identified with a common value in one of the columns name. Further, I want to consider only those records that have a certain value in the column type.

name weight type

1 $ 12.00 A

2 $ 7.00 B

2 $ 7.00 A

1 $ 1.00 C

2 $ 7.00 B

1 $ 1.00 C

2 $ 7.00 B

1 $ 7.00 B

2 $ 7.00 C

2 $ 7.00 B

开发者_如何转开发

I want the total weight for name 2, for the types A and B. Can a subquery be written for this or only looping can be done. Tnx.

SELECT
    Name,
    Type,
    SUM(Weight)
FROM
    MyTable
GROUP BY
    Name,
    Type
HAVING
    Name = @name
AND
    Type = @type

Or to be specific for your request

SELECT
    Name,
    Type,
    SUM(Weight)
FROM
    MyTable
GROUP BY
    Name,
    Type
HAVING
    Name = '2'
AND
    Type IN('A', 'B')

However, if it is purely a single value you are after for your filter then you can just SUM with a WHERE clause.

SELECT
    SUM(Weight)
FROM
    MyTable
WHERE
    Name = '2'
AND
    Type IN('A', 'B')

this really depends on ones interpretation of the OPs question, which is a little vague to me. But give this a try:

SELECT
    SUM(Weight)
    FROM MyTable
    WHERE Name=2 AND Type IN ('A','B','C')

This will show you the sum of weights for each name, where the type is A or B:

select name, sum(weight) as WeightSum
from MyTable t
where type in ('A', 'B')
group by name