SQL Server aggregate function with sum

Question

I want to use an aggregate function in SQL Server to sum the number of seats:

The table is like that (it's all the same software just version changes)

 OrderID |  CustomerID | ProductID  | Product Name       |    No of Seats

    1       |  11         | 351-0212-4 | soft v601,Download  |  3
    2       |  11         | 361-0313-5 | soft v701,Upgrade   |  2
    3       |  12         | 341-1210-4 | soft v501,Download  |  5
    4       |  12         | 351-0212-5 | soft v601,Upgrade   |  2
    ...

And I want a result like

 Sum(no of seats) 
   8

So If a customer already bought the software but have upgraded keep number of seats for the customer.

e.g.:

Customer 11 bought 3 licences of our soft and then he bought two upgrades of a newer vesion so the sum for him should be 3 instead of 5.

Is that something possible to do in SQL ? I hope I've been clear if not let me know.

Thanks in advance.

So a download is a purchase of seats, but an upgrade isn't? What if the same customer has more than one download? Are there other types of product, apart from download and upgrade? Can a customer ever purchase more seats with an upgrade? Do you have a Product table with these details on? — user359040
– user359040, Commented Feb 18, 2015 at 18:12
So what is the issue? Just filter out the upgrades before aggregating the results. It would be easier to sum only downloads if the words Download and Upgrade were stored in a separate column rather than attached to a product name. — Andriy M
– Andriy M, Commented Feb 18, 2015 at 18:17

roman · Accepted Answer · 2015-02-18 18:18:16Z

1

something like

select CustomerID, sum([No of Seats])
from <your table>
where [Product Name] not like '%upgrade%'
group by CustomerID

But in general - filter out those you don't want to see in the results and then sum. And if you want total number (not per customer):

select sum([No of Seats])
from <your table>
where [Product Name] not like '%upgrade%'

answered Feb 18, 2015 at 18:18

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jonh · Accepted Answer · 2015-02-18 18:19:54Z

0

you should add boolean column, like 'isActive', then your select can be like this

select customerid, sum(numberOfSeats) from table
where isActive = 1 
group by customerid

answered Feb 18, 2015 at 18:19

jonh

1

Comments

John Bollinger · Accepted Answer · 2015-02-18 18:23:13Z

You have some normalization problems. The Product Name column is (presumably) redundant with the ProductID column, plus Product Name apparently carries two logically distinct pieces of information: the name itself, and whether that product is an upgrade.

It would be better to split this into two tables, say Products and Orders. The Products table would have columns ProductID (primary key), Product_Name, and Is_Upgrade, and the Orders table would have columns OrderID (primary key), CustomerID (foreign key), ProductID (foreign key), and NumberOfSeats.

Given what you now have, however, and assuming that you want to avoid counting seats where the product name ends in 'Upgrade', you seem to want a query along these lines:

SELECT SUM("No of seats")
FROM Orders
WHERE CustomerID = 11 AND "Product Name" NOT LIKE '%Upgrade'

Collectives™ on Stack Overflow

SQL Server aggregate function with sum

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related