How to Fixed this Query - Bandi

Posted by Jayakumars under Sql Server on 9/2/2015 | Points: 10 | Views : 1330 | Status : [Member] [MVP] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi
Bandi

How to fixed this?

Create Table Head1
(
id int primary key identity(1,1),
BillNo int
)

--Drop table det1

Create Table det1
(
id int primary key identity(1,1),
ProductName varchar(40),
BillNo int,
Qty int,
Rate decimal(18,2),
Amount decimal(18,2)
)

--Insert into Head1 values(1001)
--Insert into Head1 values(1002)
--Insert into Head1 values(1003)
--Insert into Head1 values(1004)
--Insert into Head1 values(1005)

--Insert into det1 values('Mouse1',1001,2,100,200)
--Insert into det1 values('Mouse2',1001,3,100,300)
--Insert into det1 values('Mouse3',1002,4,100,400)
--Insert into det1 values('Mouse4',1005,2,100,200)
--Insert into det1 values('Mouse5',1003,8,100,800)
--Insert into det1 values('Mouse6',1003,5,100,500)

Select * from Head1
Select * from det1

-- My Query This

Select e.Id,e.BillNo,d.ProductName,
(SELECT sum(d.Amount)as amount FROM det1 f where f.BillNo=e.BillNo)
from det1 d inner join Head1 e on
d.BillNo=e.BillNo group by e.Id,e.BillNo,d.ProductName

-- Error
--Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

-- My output this

--Sno BillNo Productname Amount
1 1001 Mouse1 500.00
2 1002 Mouse3 400.00
3 1003 Mouse5 13000.00

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Rajnilari2015 on: 9/22/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

1	Try this SELECT Sno = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) ,X.BillNo ,X.ProductName ,X.Amount FROM ( SELECT Sno = ROW_NUMBER() OVER (PARTITION BY d.BillNo ORDER BY (SELECT 1)) ,d.BillNo ,d.ProductName ,Amount = SUM(d.Amount) OVER(PARTITION BY d.BillNo) FROM det1 d ) AS X JOIN Head1 h ON X.BillNo = h.BillNo WHERE X.Sno = 1 /* Output Sno BillNo ProductName Amount 1 1001 Mouse1 500.00 2 1002 Mouse3 400.00 3 1003 Mouse5 1300.00 4 1005 Mouse4 200.00 / -- Thanks & Regards, RNA Team Jayakumars, if this helps please login to Mark As Answer*. \| Alert Moderator

Try this

SELECT 
	Sno = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
	,X.BillNo
	,X.ProductName
	,X.Amount

FROM (
	SELECT	
	     Sno = ROW_NUMBER() OVER (PARTITION BY d.BillNo ORDER BY (SELECT 1))
	    ,d.BillNo
	   ,d.ProductName
	   ,Amount = SUM(d.Amount) OVER(PARTITION BY d.BillNo)
	FROM det1 d
	) AS X JOIN Head1 h ON X.BillNo = h.BillNo WHERE X.Sno = 1

/* Output

Sno BillNo ProductName Amount
1 1001 Mouse1 500.00
2 1002 Mouse3 400.00
3 1003 Mouse5 1300.00
4 1005 Mouse4 200.00

*/

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 9/22/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

1	However, if you want dito as the output you have suggested, just make one more level of Join based on BillNumber and ID as under SELECT Y.* FROM( SELECT Sno = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) ,X.BillNo ,X.ProductName ,X.Amount FROM ( SELECT Sno = ROW_NUMBER() OVER (PARTITION BY d.BillNo ORDER BY (SELECT 1)) ,d.BillNo ,d.ProductName ,Amount = SUM(d.Amount) OVER(PARTITION BY d.BillNo) FROM det1 d ) AS X JOIN Head1 h ON X.BillNo = h.BillNo WHERE X.Sno = 1) AS Y JOIN Head1 h ON Y.BillNo = h.BillNo AND Y.Sno = h.id /output Sno BillNo ProductName Amount 1 1001 Mouse1 500.00 2 1002 Mouse3 400.00 3 1003 Mouse5 1300.00 / -- Thanks & Regards, RNA Team Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

However, if you want dito as the output you have suggested, just make one more level of Join based on BillNumber and ID as under

SELECT Y.*

FROM(

	SELECT 

		Sno = ROW_NUMBER() OVER (ORDER BY (SELECT 1))

		,X.BillNo

		,X.ProductName

		,X.Amount



	FROM (

	      SELECT	

		  Sno = ROW_NUMBER() OVER (PARTITION BY d.BillNo ORDER BY (SELECT 1))

		  ,d.BillNo

		  ,d.ProductName

		 ,Amount = SUM(d.Amount) OVER(PARTITION BY d.BillNo)

	     FROM det1 d

	) AS X JOIN Head1 h ON X.BillNo = h.BillNo WHERE X.Sno = 1) AS Y 

JOIN Head1 h ON Y.BillNo = h.BillNo AND Y.Sno = h.id

/*output

Sno BillNo ProductName Amount
1 1001 Mouse1 500.00
2 1002 Mouse3 400.00
3 1003 Mouse5 1300.00

*/

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Manub22 on: 9/22/2015 [Member] Starter | Points: 25

0	What about "Mouse4"? And why you don't want to see Mouse2 & Mouse6? What is your logic? Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts