CODEWITHSUNDEEP
sqlsql-server
Huster
Huster

Reputation: 327

SQL Server : most common value

I have a table called Accessoires_OrderDetails with the following structure:

ID int IDENTITY(1,1)
accessoireID int,
QteSelled int,
Price Varchar(50)

I am using this query to display the most sold item in the table:

SELECT TOP 12 
    AccessoireID, COUNT(AccessoireID) AS Amount 
FROM 
    Accessoires_OrderDetails
GROUP BY 
    AccessoireID
ORDER BY 
    Amount DESC

But I don't know how to display the Qte sold.

The output of that query is :

1   3
4   2
2   1

I want the show that item id = 1 was sold 3 times.

Example:

AccessoireID = 1, Sold = 3 times, first time (Qte = 2), second time (Qte = 3), third time (Qte = 5)

The output should be

1   3  10 
4   2
2   1

So 10 is the number of Qte sold for all the sales.

Upvotes: 0

Views: 88

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Simple SUM will do:

select TOP 12 
    AccessoireID,
    count(AccessoireID) as Amount,
    SUM(QTE) as total_qte           -- Added this
from Accessoires_OrderDetails
GROUP BY AccessoireID
ORDER BY Amount
DESC

Upvotes: 1

Related Questions