-1

I need to get Total for each Customer

I have a table like this.

    Create table mytbl 
    (Branch varchar(10),
     CustCode varchar(10),
     Name varchar(10),
     Address varchar(10),
     MarketingCode varchar(10),
     CCVal varchar(10),
     Tot1 int,
     Tot2 int,
     Tot3 int)

Sample data

INSERT INTO mytbl (Branch,CustCode,Name,Address,MarketingCode,CCVal,Tot1,Tot2,Tot3) 
           VALUES 
           ('1','5001','MY C NAME','SA','12','10',100, 0, 0),
           ('1','5001','MY C NAME','SA','13','5',0, 300, 0),
           ('1','5001','MY C NAME','SA','15','8',50, 0, 0)

I need to get Sub Total for Tot1, Tot2 and Tot3 For each customer Group by Branch,CustCode

I have written query like

SELECT *, 
       SUM(Tot1) As t1,
       SUM(Tot2) As t2,
       SUM(Tot3) As t3 
from  mytbl 
GROUP BY Branch,CustCode  
WITH ROLLUP

It shows the following error

Column 'mytbl.Address' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

0

"SELECT *" is the problem.

Yo must have something like this:

SELECT Branch,CustCode, SUM(Tot1) As t1,SUM(Tot2) As t2,SUM(Tot3) As t3 from  mytbl GROUP BY Branch,CustCode  WITH ROLLUP
  • But i need to show all the columns Branch,CustCode,Name,Address,MarketingCode,CCVal – Hisanth Nov 5 at 3:18
  • That is why I said "something like this", the original issue was the "*" – JuanCa Nov 5 at 14:48
0

Pls have a look at this for bit more understanding on GROUP.

GROUP BY (even with GROUPING SET) clause required all the column to be included that are mentioned in SELECT clause excluding the ones inside aggregation function (in your case SUM).

Hope following is the one that you looking for, for more examples and test:

SELECT   Branch, CustCode, name,
         SUM(Tot1) As t1,
         SUM(Tot2) As t2,
         SUM(Tot3) As t3 
from  mytbl 
GROUP BY GROUPING SETS (ROLLUP(Branch, CustCode, name))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.