CODEWITHSUNDEEP
sql-server
Makmak
Makmak

Reputation: 19

Sum total column

I don't know if my query is correct. I want to group all the dates as one, but my query shows a null.

SELECT CustomerID,Cashier, OrderDate,SUM(Total) as totalprice
FROM SalesandReports
GROUP BY CustomerID, Cashier, OrderDate WITH ROLLUP

This is the output of my query:

This is the output of my query

Upvotes: 1

Views: 111

Answers (4)

Amit chauhan
Amit chauhan

Reputation: 540

You can do a trick here:

SELECT
   max(CustomerID) as CustomerId,
   max(Cashier) as Cashier,
   coalsce(OrderDate,'Other Dates'),
   SUM(Total) as totalprice
FROM
   SalesandReports
GROUP BY
   OrderDate WITH ROLLUP

By this GROUP BY, it will only be done by OrderDate.

Reference: the answers from Stack Overflow question Select multiple columns from a table, but group by one.

Upvotes: 0

Rajan Mishra
Rajan Mishra

Reputation: 1178

Maybe this can work for you:

SELECT
    CustomerID,Cashier,
    OrderDate,
    SUM(Total) as totalprice
FROM
    SalesandReports
GROUP BY
    OrderDate,
    CustomerID,
    Cashier
WITH ROLLUP

By making Orderdate first, the group by will be done first according to it and then on customerid and cashier.

Upvotes: 0

Aakash Singh
Aakash Singh

Reputation: 1060

Try this my friend,

SELECT 
CustomerID
,Cashier
, OrderDate
,SUM(ISNULL(totalprice,0)) as totalprice
FROM SalesandReports
WHERE  OrderDate is not NULL  
GROUP BY CustomerID, Cashier, OrderDate

Upvotes: 0

DancingFool
DancingFool

Reputation: 1267

Try your original query without the WITH ROLLUP on the end - it sounds like you don't actually want rollup, just grouping

Upvotes: 1

Related Questions