Sum function sql statement

[Loh Peggie]

Hey guys do you know how to implement the sql statement for the sum unit price in jsp?

This is my code:

"SELECT * FROM shoppingcartitems sci INNER JOIN products p ON sci.ProductId = p.ProductId where sci.UserId = ?"

But the problem is I do not know how to add in the sum UnitPrice.


Thanks:)

[Paul Sturrock]

Aggregate functions just take the field you are interested in as a parameter. So:

select sum(myField) from myTable group by [the other fields]

notice you will have to add a group by for all fields not in the function for it to work.

[Loh Peggie]

However my table is in t form of inner join.

Currently I am doing a shopping cart and I need to make use of inner join statement to display the details.

The two database that I make use of is called:
shoppingcartitems
Products
I link them together by using the productId:)

My shoppingcartitems attributes are:
SCId
UserId
ProductId
Quantity

My products attributes are:
ProductId
ProdName
ProdColor
ProdDesc
UnitPrice
Quantity
ProdCatId
UserId
ProdImage

The shopping cart will display ProdImage, UnitPrice, ProductName, quantity and TotalPrice(But i dont know how to write the sql statement that sum the unit price from the database)

Thanks:):)

[Paul Sturrock]

The join doesn't make a difference, the logic is still the same. To flesh it out a bit:

select sum(your filed you are adding), otherfield1, otherfield2, .... otherfieldN
from table1 inner join table2 on [join condition]
where [your other conditions]
group by [all fields not in you aggregate function]

[Loh Peggie]

Thanks:)

Erm may I know is my sql statement correct:

SELECT SUM(UnitPrice), ProdImage, ProdName, Quantity FROM shoppingcartitems sci INNER JOIN products p ON sci.ProductId = p.ProductId where sci.UserId = '?' GROUP BY UnitPrice"

Thanks!

[Paul Sturrock]

No, you need to group things not in your aggregate function.