Click here to Skip to main content
15,357,967 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I want row wise and column wise Total

SQL
Create table #Sections (SecID int, Secnam varchar(50))
create table #ItemMasterFile(CodeItem int, Descriptionitem varchar(max),SecID int,CID int)
create table #Probale (BID int, CodeItem int,   prdqtyint,EntryDate date)


insert into #Sections 
values
(1, 'HR'),
(2, 'Baby'),
(3, 'Ladies'),
(4, 'Mix Rammage'),

insert into #ItemMasterFile
values
(1, 'A',  '1'),
(2, 'B',  '2'),
(3, 'C',  '3'), 
(4, 'D' ,'4')


insert into #Probale
values
(1, '1', '1', '5/01/2019 '),
(2, '2', '1', '5/01/2019 '),
(3, '3', '1', '5/01/2019 '),
(4, '2', '1', '5/02/2019 '),
(5, '3', '1', '5/02/2019 '),
(6, '4', '1', '5/02/2019 '),
(6, '1', '1', '5/03/2019 '),
(7, '2', '1', '5/04/2019 '),
(8, '4', '1', '5/05/2019 '),


What I have tried:

here is my store procedure

SQL
Create Procedure [dbo].[Pivot_Item1] @StartDate Date,
@Enddate Date

AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

SELECT Distinct Entrydate INTO #Dates26 FROM Probale WHERE EntryDate BETWEEN @StartDate AND @Enddate ORDER BY EntryDate

SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(2),EntryDate) )
FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate

--SET @cols = STUFF(@cols, 1, 1, '')


SET @query =
N'SELECT *
FROM (SELECT Descriptionitem,Probale.prdqty,
DATEPART(DAY, Probale.EntryDate)as DDate
FROM Probale left JOIN ItemMasterFile on ItemMasterFile.Codeitem=Probale.Codeitem
where Probale.DelID is Null and Probale.EntryDate BETWEEN @StartDate AND @Enddate )prd
PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat'
exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate

END
Posted
Updated 19-Jun-19 22:51pm
v2
Comments
OriginalGriff 20-Jun-19 3:13am
   
And?
What does it do that you didn't expect, or not do that you did?
What help do you need?
akhter86 20-Jun-19 3:37am
   
Dear OriginalGriff,
I want Row and Column wise Total ,,above store procedure just display date wise Total of item ,,Now i want Rows Total or each item and Column wise Total of each Date.
OriginalGriff 20-Jun-19 3:44am
   
And what have *you* tried in order to do that?
What did that produce, and what did you do to try and fix it?
What errors did you get?
akhter86 20-Jun-19 4:09am
   
Dear OriginalGriff,
i tried this ( ALTER Procedure [dbo].[Pivot_Item2]
@StartDate Date,
@Enddate Date

AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
declare @sum nvarchar(max);
SELECT @sum= COALESCE(@sum + ',','') + ('sum('+QUOTENAME(prdqty)+')') from Probale;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;




SELECT Distinct Entrydate INTO #Dates26
FROM Probale
WHERE EntryDate BETWEEN @StartDate AND @Enddate
ORDER BY EntryDate



SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(10),EntryDate) )
--FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
FROM (SELECT DISTINCT EntryDate AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate

--SET @cols = STUFF(@cols, 1, 1, '')


SET @query =
N'with cte as (SELECT *,(select sum(prdqty) from Probale where CONVERT(varchar(10), EntryDate) =stat.ddate ) as total


FROM (SELECT Descriptionitem,Probale.prdqty,

CONVERT(varchar(10),Probale.EntryDate) as DDate
FROM Probale left JOIN ItemMasterFile on ItemMasterFile.Codeitem=Probale.Codeitem
where Probale.DelID is Null and Probale.EntryDate BETWEEN @StartDate AND @Enddate )prd
PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat)

select * from cte

union all
select 0,'+@sum+', (select sum(prdqty ) from Probale ) from cte'

exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate


END
)

error is coming ( Invalid column name 'ddate'.)

1 solution

Firstly you need to fix the code you are using to create the tables - currently it produces these errors
Msg 173, Level 15, State 1, Line 3
The definition for column 'prdqtyint' must include a data type.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'insert'.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near ','.
Once you fixed those you will then get a further error
Msg 213, Level 16, State 1, Line 13
Column name or number of supplied values does not match table definition.
Because the values you are inserting into #ItemMasterFile do not match the definition of the table. You've also done that with #Probale where you are passing varchar values into int columns but SQL has converted that for you.

I commend you for giving us code to recreate your data tables, but for future reference your lack of care on this will make many members just give up.

The problem persists when I start to examine your dynamic query -
Msg 207, Level 16, State 1, Line 78
Invalid column name 'DelID'.
I just deleted that from my version.

Incidentally, I also dislike the inconsistency of
SQL
exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate
EntryDate is a date so why start using datetime. So I finally got your results of
Descriptionitem	1	2	3	4	5
A	1	NULL	1	NULL	NULL
B	1	1	NULL	1	NULL
C	1	1	NULL	NULL	NULL
D	NULL	1	NULL	NULL	1
This is where @OriginalGriff's comments come into play. You haven't made any effort to do this for yourself. So …

To get the row total you will need to add all of the columns together. Use the same technique you used to get @cols but note that you will have to deal with those NULL values - I suggest using something like
SELECT Descriptionitem, ISNULL([1],0) AS [1], … etc
To get the column totals you will need to UNION a query that just totals the columns. I put your pivot into a Common Table Expression to make this easier to do and added
UNION 
SELECT 'Total', SUM([1]), SUM([2]),SUM([3]),sum([4]),sum([5]), SUM(([1] + [2] + [3] + [4] + [5])) AS RowTotal
from CTE
Note I don't have to worry about ISNULL because SUM will handle null values for me.
But also note that I had to include the sum of the rowtotals to make the union work
   
Comments
akhter86 20-Jun-19 4:58am
   
Dear Chill60

I have done UNION QUERY here is my in store procedure ...

i tried this ( ALTER Procedure [dbo].[Pivot_Item2]
@StartDate Date,
@Enddate Date

AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
declare @sum nvarchar(max);
SELECT @sum= COALESCE(@sum + ',','') + ('sum('+QUOTENAME(prdqty)+')') from Probale;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;




SELECT Distinct Entrydate INTO #Dates26
FROM Probale
WHERE EntryDate BETWEEN @StartDate AND @Enddate
ORDER BY EntryDate



SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(10),EntryDate) )
--FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
FROM (SELECT DISTINCT EntryDate AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate

--SET @cols = STUFF(@cols, 1, 1, '')


SET @query =
N'with cte as (SELECT *,(select sum(prdqty) from Probale where CONVERT(varchar(10), EntryDate) =stat.ddate ) as total


FROM (SELECT Descriptionitem,Probale.prdqty,

CONVERT(varchar(10),Probale.EntryDate) as DDate
FROM Probale left JOIN ItemMasterFile on ItemMasterFile.Codeitem=Probale.Codeitem
where Probale.DelID is Null and Probale.EntryDate BETWEEN @StartDate AND @Enddate )prd
PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat)

select * from cte

union all
select 0,'+@sum+', (select sum(prdqty ) from Probale ) from cte'

exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate


END
)
but
error is coming ( Invalid column name 'ddate'.)
CHill60 20-Jun-19 7:55am
   
Firstly have a look at your variable @sumIt's value is
sum([1]),sum([1]),sum([1]),sum([1]),sum([1]),sum([1]),sum([1]),sum([1]),sum([1])
. Not what you want at all. Move that down to after you have populated #Dates26 and use the correct table i.e.
SELECT @sum= COALESCE(@sum + ',','') + ('sum('+QUOTENAME(Entrydate)+')') FROM (SELECT DISTINCT EntryDate AS EntryDate FROM #Dates26 ) TORDER BY EntryDate
which will give you
[2019-05-01],[2019-05-02],[2019-05-03],[2019-05-04],[2019-05-05]
You will also need another variable which will give you
ISNULL([2019-05-01],0) AS [2019-05-01]...etc
Use the same technique. You have also completely ignored the point about ISNULL and having to add the columns together to get the row total
Having solved all of that put your originalpivot back into the body of the CTE - I have no idea what you are trying to achieve with those sub-queries. Get rid of them.
Your final query should look something like
;with cte as 
(	
     SELECT Descriptionitem, ISNULL([2019-05-01],0) AS [2019-05-01], ISNULL([2019-05-02],0) AS [2019-05-02],
	 		ISNULL([2019-05-03],0) AS [2019-05-03], ISNULL([2019-05-04],0) AS [2019-05-04],
 		ISNULL([2019-05-05],0) AS [2019-05-05]	
 	FROM 	
 	(		
 	 	SELECT Descriptionitem,#Probale.prdqty, #Probale.EntryDate as DDate		
 	 	FROM #Probale 		
 	 	left JOIN #ItemMasterFile on #ItemMasterFile.Codeitem=#Probale.Codeitem		
 	 	where --#Probale.DelID is Null and 
 	 	#Probale.EntryDate BETWEEN @StartDate AND @Enddate 	
 	)prd	
 	PIVOT 	
 	(		
 	 	Sum(prdqty) FOR DDate IN ([2019-05-01],[2019-05-02],[2019-05-03],[2019-05-04],[2019-05-05])	
 	) AS stat
)
select Descriptionitem, [2019-05-01],[2019-05-02],[2019-05-03],[2019-05-04],[2019-05-05],  		
([2019-05-01]+[2019-05-02]+[2019-05-03]+[2019-05-04]+[2019-05-05]) AS RowTotal
from cte
union
select 'Total',sum([2019-05-01]),sum([2019-05-02]),sum([2019-05-03]),sum([2019-05-04]),sum([2019-05-05]), 		
SUM([2019-05-01]+[2019-05-02]+[2019-05-03]+[2019-05-04]+[2019-05-05]) as ColumnTotal
from cte

Use
PRINT @query
instead of the exec until you have the query right and use a lot more CRLF and whitespace - it's a lot easier to see what is going on
akhter86 21-Jun-19 14:15pm
   
thanks for your guidance alot ,, i never used hardcore values in query,,no idea that how it will get done [2019-05-01],[2019-05-02],[2019-05-03],[2019-05-04],[2019-05-05], so still looking for solution ,,,
CHill60 25-Jun-19 7:41am
   
You already created that list of column names in your original post!
SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(2),EntryDate) )
FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate
The only difference is in your 2nd attempt in the comments you no longer use DATEPART and have used the entire date as a column name. I don't understand why you are still looking for a solution
Maciej Los 20-Jun-19 5:08am
   
5ed!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900