How to use with Rollup Function in sql Query?

Question

0.00/5 (No votes)

See more:

Hello everyone,
Here i have desined a sql query using with rollup .
Query like this..

SQL

select ROW_NUMBER() OVER (ORDER BY src.INVOICENO ) SRNO,
 ISNULL(CONVERT(varchar,Date,103),'')DATE,ISNULL(INVOICENO,'')INVOICENO,ISNULL(CustName,'')PARTY_NAME,ISNULL(DeliveryAddr,'')DeliveryAddr,ISNULL(Grade,'')GRADE,ISNULL(Details,'GRANDTOTAL')DETAILS,QTY,ISNULL(RATE,0.0000)RATE,ASSESSABLE_Value,BED,EDCESS,HECESS,VAT,RoundOff,TOTALAMOUNT

from
(
  SELECT 
  ExDcDt AS Date,ExDcNo AS INVOICENO,CustName,DeliverAdd1 As DeliveryAddr,ItemDesc AS Grade,itemDescription as Details, SUM (Qty) QTY,RATE,SUM(Amount) AS ASSESSABLE_Value,SUM(BasicDuty) AS BED,SUM(ExEdu) AS EDCESS,SUM(ExHEC) AS HECESS,SUM(Vat) AS VAT,SUM(RoundOff) AS RoundOff,SUM(TotalAmt) AS TOTALAMOUNT  from SalesMaster where( ExDcDt>=CONVERT(VARCHAR,'2015-DEC-11', 103) and ExDcDt<=CONVERT(VARCHAR,'2015-DEC-17', 103))
  GROUP BY ExDcNo, ExDcDt,DeliverAdd1,CustName,ItemDesc ,itemDescription,Rate with rollup 
) src
where Rate  is not null
or
(
  Date is null 
  and INVOICENO is null
  --and CustName is null
)

Posted 17-Dec-15 22:19pm

SujataJK

Add a Solution

Comments

SujataJK 18-Dec-15 4:25am

Here I have used with rollup for calculating sum of perticular column at bottom ,I got result but grand total row should be at bottom of query result Instead my result it display at top.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-12-17T23:01:00

You need to order your query, but of course the problem becomes what to order it by!

You can use CASE in an ORDER BY clause to achieve this e.g.

SQL

ORDER BY CASE WHEN INVOICENO <> 0 THEN INVOICENO ELSE ASSESSABLE_VALUE END

I chose this specific things because I observed that INVOICENO on my test data was 0 for the Grand Total row - you might need to fiddle with it a bit.

Note that because of this re-ordering the values for SRNO start at 2, go through to the number of records and then show 1. The easiest way to get this showing 1 through number of rows again is to use

select ROW_NUMBER() OVER (ORDER BY src.INVOICENO ) - 1 SRNO,

. If you do this then the Grand Total with have SRNO = 0

[EDIT after OP comments]
I've used a cut-down version of your query to demonstrate a couple of ways of getting the row number to reflect the new order, and I've stripped out the where clauses purely for this demo

SQL

WITH CTE AS 
(
	SELECT ROW_NUMBER() OVER (ORDER BY INVOICENO ) SRNO, ISNULL(INVOICENO,'') INVOICENO, 
	ASSESSABLE_Value,TOTALAMOUNT --,ETC
 	from
	(
	  SELECT 
	  ExDcNo AS INVOICENO,SUM(Amount) AS ASSESSABLE_Value,SUM(TotalAmt) AS TOTALAMOUNT  
	  from SalesMaster 
	  GROUP BY ExDcNo with rollup 
	) SRC
),
CTE2 AS 
(
	SELECT MAX(ExDcNo) + 1 ino FROM SalesMaster -- WHERE etc
	-- may have to fiddle with this bit if ExDcNo is not an number
)
select CASE WHEN CTE.SRNO = 1 THEN CTE2.ino ELSE CTE.SRNO - 1 END AS SRNO,
INVOICENO, ASSESSABLE_Value, TOTALAMOUNT -- etc 
FROM CTE, CTE2
ORDER BY 1


-- OR 


DECLARE @GT_INVOICE int = (SELECT MAX(ExDcNo) FROM SalesMaster) + 1 -- insert the where etc
-- again if ExDcNo is not a number find a way of increasing this value past the last one used
;WITH CTE AS 
(
	SELECT ROW_NUMBER() OVER (ORDER BY INVOICENO ) SRNO, ISNULL(INVOICENO,'') INVOICENO, 
	ASSESSABLE_Value,TOTALAMOUNT --,ETC
 	from
	(
	  SELECT 
	  ExDcNo AS INVOICENO,SUM(Amount) AS ASSESSABLE_Value,SUM(TotalAmt) AS TOTALAMOUNT  
	  from SalesMaster 
	  GROUP BY ExDcNo with rollup 
	) SRC
)
select CASE WHEN CTE.SRNO = 1 THEN @GT_INVOICE ELSE SRNO - 1 END AS SRNO,
INVOICENO, ASSESSABLE_Value, TOTALAMOUNT -- etc 
FROM CTE
ORDER BY 1