How to Fixed this Query - Bandi

Posted by Jayakumars under Sql Server on 9/2/2015 | Points: 10 | Views : 436 | Status : [Member] [MVP] | Replies : 3
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


Responses

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

Up
1
Down
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

Up
1
Down
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

Up
0
Down
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

Login to post response