can i use distinct but still get all values? 1

[andycape]

I want to use distinct in an equasion so that a line only shows to the page once, but how do i get values from all lines in the database.

so if I have these lines in the database:

table headings : fruit, cost, amount
potatoes, 10, 10
potatoes, 20, 10
potatoes, 15, 15
potatoes, 17, 10

I want only potatoes to show to the screen once (so I will use "select distinct fruit&quot, but i want the sum of the amount column (10+10+15+10).

any ideas how i get the sum of amount column?

 

[JamesLean]

Don't use DISTINCT:

SELECT fruit, SUM(amount) AS totalAmount
FROM tbl
GROUP BY fruit

--James

 

[Glyndwr]

andycape

try a group by clause

create table #fruit ( fruit varchar(40), cost money ,tax money)
go
insert into #fruit values ('potatoes', 100,17.5)
insert into #fruit values ('potatoes', 200,35)
insert into #fruit values ('potatoes', 300,52.5)
go

select fruit, sum(cost + tax) 'Total Amount'
from #fruit
group by fruit


Results:

fruit                                    Total Amount          
---------------------------------------- --------------------- 
potatoes                                             1410.0000

Glyndwr

 

[andycape]

Thanx - i see what you mean, I think i didnt use this method though because I have to get a value from each line.

ie:
potatoes, 10, 10 (I need 10 *10)
potatoes, 20, 10 (I need 20 *10)
potatoes, 15, 15 (I need 15 *15)
potatoes, 17, 10 (I need 17 *10)

and then the final figure i need is the sum of those 4 calculations. Is it still possible to do this using group by ?

 

[JamesLean]

SELECT fruit, SUM(amount * cost) AS total
FROM tbl
GROUP BY fruit

--James

 

[andycape]

done, thanx !
I should have thought of that.