Sum total column

Question

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:

do your problem solved or not – Amit chauhan Oct 06 '18 at 19:45 — Amit chauhan, Oct 06 '18 at 19:45

score 1 · Answer 1 · answered Oct 03 '18 at 04:57

1

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

answered Oct 03 '18 at 04:57

DancingFool

1,247
1
8
10

But i want to get the total of all the columns of total price and display it at the buttom – Makmak Oct 03 '18 at 05:00

score 0 · Answer 2 · edited Oct 06 '18 at 13:18

0

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.

edited Oct 06 '18 at 13:18

Peter Mortensen

30,738
21
105
131

answered Oct 03 '18 at 04:05

Rajan Mishra

1,178
2
14
30

score 0 · Answer 3 · edited Oct 06 '18 at 13:28

0

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.

edited Oct 06 '18 at 13:28

Peter Mortensen

30,738
21
105
131

answered Oct 03 '18 at 04:17

Amit chauhan

540
9
22

Aakash Singh · Answer 4 · 2018-10-03T05:26:39.987

0

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

edited Oct 03 '18 at 05:26

answered Oct 03 '18 at 05:11

Aakash Singh

1,032
8
27

theres no output when i try your query – Makmak Oct 03 '18 at 05:15
change OrderDate != NULL into OrderDate is not NULL – Aakash Singh Oct 03 '18 at 05:17
where i can put that? – Makmak Oct 03 '18 at 05:20
Still not total the total price column – Makmak Oct 03 '18 at 05:22
I need to show the sum of totalprice column in the buttom – Makmak Oct 03 '18 at 05:22
No theres no error showing it show correctly but what i need to total the column of total price – Makmak Oct 03 '18 at 05:24

Sum total column

4 Answers4