asked on

SQL Server - Add multiple sums together from different Item Numbers.

Hello, All;

So. This is a Shopping cart, and what we are doing is calculating ALL items per row, and then we need to add them together.
(Sample with two products with multiple(s) of each product
keeping in mind, that the shopping cart, just like any other, can have ONE or 100's of items in it.)

item cost: $16.99 (Amount)
Total of items: 15 (ProductTotal)
= $254.85

item cost: $3.99 (Amount)
Total of items: 6 (ProductTotal)
= $23.94

The MemID is the only thing that will be consistant.
The Item_No however, will change for each product the customer adds.

select sum(Amount)*sum(ProductTotal) as SubTotal from Cart where MemID='z6GQ9UPF55JeSlWDRbib' and Item_No='HL-00000116' --  $254.85
select sum(Amount)*sum(ProductTotal) as SubTotal from Cart where MemID='z6GQ9UPF55JeSlWDRbib' and Item_No='HL-00000005' --  $23.94

Open in new window

OK, what I would LIKE to accomplish, is to have all the items that are in the shopping cart, added together, in one SQL Statement.
Instead of doing it in ASP Classic afterward.

Any suggestions on this would be great.
This it the last part of the Shopping Cart, and it is completed.

Thanks in Advance.
Carrzkiss

OMC2000

try this

select Item_No, sum(Amount)*sum(ProductTotal) as SubTotal from Cart where MemID='z6GQ9UPF55JeSlWDRbib' and Item_No in ( 'HL-00000116','HL-00000005') group by Item_No

Open in new window

Wayne Barron

ASKER

Does not give the TOTAL of both products as a WHOLE Number balance.
Also, as I stated above, I cannot use the Item_No, as it is changing.
the MemID is the only thing that is going to be the same in the shopping cart.

ASKER CERTIFIED SOLUTION

OMC2000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wayne Barron

ASKER

OK, that works.
But, how am I going to get all the item_No's?

This is my query, with your code added.
I cannot have the actual numbers in the sql, so, how am I going to get all the number.
Also, how can I get the last number the [Total] number, as when you run this in the asp page, it gets the top number.

Set sqlchOut = Server.CreateObject("ADODB.Command")
sqlchOut.ActiveConnection=MusicConn
sqlchOut.Prepared = true
sqlchOut.commandtext="select Item_No, sum(Amount)*sum(ProductTotal) as SubTotal from Cart where MemID=? and Item_No in ( 'HL-00000116','HL-00000005','HL-00000002') group by Item_No union all select 'Total', sum(SubTotal) from ( select sum(Amount)*sum(ProductTotal) as SubTotal from Cart where MemID=? and Item_No in ( 'HL-00000116','HL-00000005','HL-00000002') group by Item_No) a"
sqlchOut.Parameters.Append sqlchOut.CreateParameter("@MemID", adVarChar, adParamInput, 50, chSession)
sqlchOut.Parameters.Append sqlchOut.CreateParameter("@MemID", adVarChar, adParamInput, 50, chSession)
set rschOut = sqlchOut.execute

Open in new window

Wayne Barron

ASKER

thinking.
If there are 25 items listed in the shopping cart.
That would be 25 numbers that would have to be challenged against the query, 2 times.
That is a HUGE task unless I am missing something here.

Wayne Barron

ASKER

The only other way to do it would be.

a=("'HL-00000116','HL-00000005','HL-00000002'")

Item_No in ("&a&")

Open in new window

Though this is not that safe unless I pull the records straight from the table.
This way, there is no possibility of an tampering.

OMC2000

I see.

Alternative solution is a stored procedure with table valued parameters:

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

SOLUTION

Wayne Barron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wayne Barron

ASKER

Thanks, once again OMC2000
Your code helped to knock out the Shopping Cart.
All it good.
Just need to finish up a few things, and this site is nearing release.

Wayne

OMC2000

Thank you Wayne for your kind words, good luck!