CODEWITHSUNDEEP
sqlsql-server
holyredbeard
holyredbeard

Reputation: 21288

Showing the total amount in one line (SQL server)

I'm trying to find out how to show the total value in a table (item*quantity) of all the items in one line. The code below shows the total of every single item on one line each, not the total value of all of them. How can this be done?

I guess I should use a subquery, but not how to design it.

Thanks in advance

SELECT CONVERT(Decimal(8,0),ROUND((quantity*price),2)) AS Total
FROM Item

EDIT: By mistage I included ITEMNAME, that shall NOT be a part of the result!

The table is designed as follows:

ITEMNAME   QUANTITY   PRICE
Table      20         100
Chair      30         50
and so on

The result is as follows:

ITEMNAME   TOTAL
Table      2000
Chair      1500

I want it this way:

TOTAL
3500

Upvotes: 1

Views: 2889

Answers (2)

Taryn
Taryn

Reputation: 247860

You need to use SUM() and GROUP BY

SELECT Itemname, CONVERT(Decimal(8,0),ROUND((SUM(quantity*price)),2)) AS Total
FROM Item
GROUP BY Itemname

You need to include the GROUP BY if you want to include additional columns.

If you just want the Total, then

SELECT SUM(quantity*price) AS Total
FROM Item

If you want the sum of all records then you can do the following, this will get the sum of all rows:

SELECT SUM(T.Total)
FROM 
(
    select sum(quantity*price) as total
    from Item
) T

If you want to add the convert:

SELECT CONVERT(Decimal(8,0),ROUND(SUM(T.Total)),2)) 
FROM 
(
    select sum(quantity*price) as total
    from Item
) T

Upvotes: 0

Ryan
Ryan

Reputation: 28247

SELECT SUM (quantity*price) AS Total
FROM Item

Upvotes: 6

Related Questions