How to execute a SQL query for range of items?

Question

0.00/5 (No votes)

See more:

, +

my database has a main table (ex. Table_A). column 1 contains name of items and column 6 is date of my data row. Column 7 is type of items of column 1 which are from A to z.

for example (some rows of Table_A):

HTML

|item_1  |200  |120  |300  |40   |201702  |type_A  |
|item_2  |210  |320  |340  |10   |201702  |type_A  |
|item_1  |150  |30   |70   |38   |201703  |type_A  |
|item_2  |315  |220  |120  |40   |201703  |type_A  |
|item_3  |40   |500  |110  |35   |201702  |type_B  |
|item_4  |758  |78   |152  |61   |201702  |type_B  |
|item_3  |778  |920  |12   |330  |201703  |type_B  |
|item_4  |315  |220  |120  |40   |201703  |type_B  |

now i want to show items from every type in column 7 from a specific date (ex.201703) and then below that sum of column 2, sum of column 3, sum of column 4, sum of column 5. and then below that rate of growth which calculate within function (((sum of month / sum of previous month)*100)-100)

and for the above example the result i want is:

HTML

|item_1  |150  |30   |70   |38   |201703  |type_A  |
|item_2  |315  |220  |120  |40   |201703  |type_A  |
|sum     |465  |250  |190  |78   |201703  |type_A  |
|rate    |13.1 |-43.1|-74.3|56   |201703  |type_A  |
|item_3  |778  |920  |12   |330  |201703  |type_B  |
|item_4  |315  |220  |120  |40   |201703  |type_B  |
|sum     |1093 |1140 |132  |370  |201703  |type_B  |
|rate    |36.96|97.23|-18.5|285.4|201703  |type_B  |

What I have tried:

(
	SELECT 	col1, col2, col3, col4, col5, col6, col7
	FROM	Table_A INNER JOIN Table_B ON Table_A.col1 = Table_B.col1
	WHERE  	Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A')
)
UNION ALL
(
	SELECT 	'sum', SUM(col2), SUM(col3), SUM(col4), SUM(col5), 201703, 'type_A'
	FROM	Table_A INNER JOIN Table_B ON Table_A.col1 = Table_B.col1
	WHERE  	Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A')
) --sum of data with 'g'
UNION ALL
(
SELECT 
	N'Rate',
	(
		ROUND
		(
			(
				(
					(
						(
							SELECT	CONVERT (FLOAT,SUM(col2))
							FROM	Table_A 
							WHERE  	Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A')
						)
						/
						(
							SELECT	CONVERT (FLOAT,SUM(col2))
							FROM	Table_A 
							WHERE  	Table_A.col6 = (201703 - 1) AND (Table_A.col7 = 'type_A')
						)
					 )*100
				)-100
			),2
		)
	)
	,
	(
		ROUND
		(
			(
				(
					(
						(
							SELECT	CONVERT (FLOAT,SUM(col3))
							FROM	Table_A 
							WHERE  	Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A')
						)
						/
						(
							SELECT	CONVERT (FLOAT,SUM(col3))
							FROM	Table_A 
							WHERE  	Table_A.col6 = (201703 - 1) AND (Table_A.col7 = 'type_A')
						)
					)*100
				)-100
			),2
		)
	)
	,
	(
		ROUND
		(
			(
				(
					(
						(
							SELECT	CONVERT (FLOAT,SUM(col4))
							FROM	Table_A 
							WHERE  	Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A')
						)
						/
						(
							SELECT	CONVERT (FLOAT,SUM(col4))
							FROM	Table_A 
							WHERE  	Table_A.col6 = (201703 - 1) AND (Table_A.col7 = 'type_A')
						)
					)*100
				)-100
			),2
		)
	)
	,
	(
		ROUND
		(
			(
				(
					(
						(
							SELECT	CONVERT (FLOAT,SUM(col5))
							FROM	Table_A 
							WHERE  	Table_A.col6 = 201703 AND (Table_A.col7 = 'type_A')
						)
						/
						(
							SELECT	CONVERT (FLOAT,SUM(col5))
							FROM	Table_A 
							WHERE  	Table_A.col6 = (201703 - 1) AND (Table_A.col7 = 'type_A')
						)
					)*100
				)-100
			),2
		)
	)
	,
	NULL
	,
	'type_A'
)

but this code shows only one type from column 7.

Posted 31-Oct-17 1:01am

Mohamadkamal

Updated 31-Oct-17 20:00pm

v7

Add a Solution

2 solutions

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)

itsmypassion · Answer 1 · 2017-10-31T02:14:00

Try followig code

SQL

SELECT COL1,COL2,COL3 ,COL4 ,COL5 ,COL6 ,COL7  FROM TABLE_A WHERE COL7='TYPE_A' AND COL6 = '201703'
UNION ALL
SELECT 'SUM' AS COL1,SUM(COL2),SUM(COL3) ,SUM(COL4) ,SUM(COL5) ,COL6 ,MAX(COL7)  FROM TABLE_A GROUP BY COL6,COL7 HAVING COL6 ='201703' AND COL7='TYPE_A'

UNION ALL

---RATE CALCULATION
SELECT A.COL1,(((A.COL2/CAST(B.COL2PREV AS FLOAT ))*100)-100) AS COL2 ,
(((A.COL3/CAST(B.COL3PREV AS FLOAT ))*100)-100) AS COL3 ,
(((A.COL4/CAST(B.COL4PREV AS FLOAT ))*100)-100) AS COL4 ,
(((A.COL5/CAST(B.COL5PREV AS FLOAT ))*100)-100) AS COL5 ,
A.COL6 AS COL6,A.COL7 AS COL7
FROM
(SELECT 'RATE' AS COL1,SUM(COL2) COL2,SUM(COL3) COL3 ,SUM(COL4) COL4,SUM(COL5) COL5 ,COL6 ,MAX(COL7) COL7  FROM TABLE_A GROUP BY COL6,COL7 HAVING COL6 ='201703' AND COL7='TYPE_A') AS A,

(SELECT 'RATE' AS COL1PREV,SUM(COL2) COL2PREV,SUM(COL3) COL3PREV ,SUM(COL4) COL4PREV,SUM(COL5) COL5PREV,COL6 ,MAX(COL7) AS COL7PREV  FROM TABLE_A GROUP BY COL6,COL7 HAVING COL6 ='201702' AND COL7='TYPE_A') AS B

UNION ALL
SELECT COL1,COL2,COL3 ,COL4 ,COL5 ,COL6 ,COL7  FROM TABLE_A WHERE COL7='TYPE_B' AND COL6 = '201703'
UNION ALL
SELECT 'SUM' AS COL1,SUM(COL2),SUM(COL3) ,SUM(COL4) ,SUM(COL5) ,COL6 ,MAX(COL7)  FROM TABLE_A GROUP BY COL6,COL7 HAVING COL6 ='201703' AND COL7='TYPE_B'
UNION ALL
SELECT A.COL1,(((A.COL2/CAST(B.COL2PREV AS FLOAT ))*100)-100) AS COL2 ,
(((A.COL3/CAST(B.COL3PREV AS FLOAT ))*100)-100) AS COL3 ,
(((A.COL4/CAST(B.COL4PREV AS FLOAT ))*100)-100) AS COL4 ,
(((A.COL5/CAST(B.COL5PREV AS FLOAT ))*100)-100) AS COL5 ,
A.COL6 AS COL6,A.COL7 AS COL7
FROM
(SELECT 'RATE' AS COL1,SUM(COL2) COL2,SUM(COL3) COL3 ,SUM(COL4) COL4,SUM(COL5) COL5 ,COL6 ,MAX(COL7) COL7  FROM TABLE_A GROUP BY COL6,COL7 HAVING COL6 ='201703' AND COL7='TYPE_B') AS A,

(SELECT 'RATE' AS COL1PREV,SUM(COL2) COL2PREV,SUM(COL3) COL3PREV ,SUM(COL4) COL4PREV,SUM(COL5) COL5PREV,COL6 ,MAX(COL7) AS COL7PREV  FROM TABLE_A GROUP BY COL6,COL7 HAVING COL6 ='201702' AND COL7='TYPE_B') AS B

Mujeebahmad · Answer 2 · 2017-10-31T20:00:00

The output you want is achieved by below SQL query..

--Prepare sample data
create table #Table_A(col1 varchar(50), col2 numeric(18,2), col3 numeric(18,2), col4 numeric(18,2), col5 numeric(18,2), col6 varchar(10), col7 varchar(50))
insert into #Table_A
values('item_1',200,120,300,40,'201702','type_A')

insert into #Table_A
values('item_2',210,320,340,10,'201702','type_A')

insert into #Table_A
values('item_1',150,30 ,70 ,38 ,'201703','type_A')

insert into #Table_A
values('item_2',315,220,120,40 ,'201703','type_A')

insert into #Table_A
values('item_3',40 ,500,110,35 ,'201702','type_B')

insert into #Table_A
values('item_4',758,78 ,152,61 ,'201702','type_B')

insert into #Table_A
values('item_3',778,920,12 ,330,'201703','type_B')

insert into #Table_A
values('item_4',315,220,120,40 ,'201703','type_B')
---------------------------------
--Input parameters---------------
Declare @Month varchar(6),@prevMonth varchar(6)
set @Month='201703'
set @prevMonth='201702'
---------------------------------
select * from (
select ROW_NUMBER() over(partition by col7 order by col1) as SlNo,col1,col2,col3,col4,col5,col6,col7 from #Table_A where col6=@Month
union all
select count(col1)+1 as SlNo,'Sum' as col1,sum(col2) as col2,sum(col3) as col3,sum(col4) as col4,sum(col5) as col5,col6,col7 from #Table_A where col6=@Month group by col6,col7
union all
select count(tmp2.col2)+2 as SlNo,'Rate' as col1,
((sum(tmp2.col2)/(
select sum(col2) from #Table_A where col6=@prevMonth and col7=tmp2.col7))*100)-100 as col2,
((sum(tmp2.col3)/(
select sum(col3) from #Table_A where col6=@prevMonth and col7=tmp2.col7))*100)-100 as col3,
((sum(tmp2.col4)/(
select sum(col4) from #Table_A where col6=@prevMonth and col7=tmp2.col7))*100)-100 as col4,
((sum(tmp2.col5)/(
select sum(col5) from #Table_A where col6=@prevMonth and col7=tmp2.col7))*100)-100 as col5,
tmp2.col6,tmp2.col7 from #Table_A tmp2 where tmp2.col6=@Month group by tmp2.col6,tmp2.col7) t order by col7,SlNo
--Drop temporary data-----
drop table #Table_A
----END-------------

How to execute a SQL query for range of items?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0