sub query with group by

Apurva Singh

7y1k

I have a stored procedure for returning data in one row with sub query,but i am getting a error that

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I am providing you my stored procedure please check and give me solution.

CREATE proc [dbo].[GetChartDetails]

@type varchar(50)

begin

select

(select SUM(PaidAmt)as LifeInsurance, DATENAME(MONTH, CreateDate) Month_name from CutomerPaymentDetail where categorytype='LIFE INSURANCE' and type=@type group by DATENAME(MONTH, CreateDate)) ,

(select SUM(PaidAmt)as GIC ,DATENAME(MONTH, CreateDate) Month_name from CutomerPaymentDetail where categorytype='GIC' and type=@type group by DATENAME(MONTH, CreateDate)) ,

(select SUM(PaidAmt)as MutualFund ,DATENAME(MONTH, CreateDate) Month_name from CutomerPaymentDetail where categorytype='MUTUAL FUND' and type=@type group by DATENAME(MONTH, CreateDate))

end

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
Hi,
try this code:
select w.userID,count(w.id)
from (select id,max(bidAmount),userID from Bids group by id, userID) w
group by w.userID
Jignesh Kumar7y ago
Hi Apurava,

You are paasing @type as parameter I think you can use partition over Category Type,
CREATE proc [dbo].[GetChartDetails]
@type varchar(50)
as
begin

select
sum(sum(PaidAmount)) over(partition by categorytype) as TotalMembership,categorytype,DATENAME(MONTH, CreateDate) as Month_name
From CutomerPaymentDetail
Group by categorytype, DATENAME(MONTH, CreateDate)

END
Suraj Kumar7y ago
Please try this query hope it will solve your problem.
Alter proc [dbo].[GetChartDetails]
@type varchar(50)
as
begin
    Select SUM(PaidAmt)as LifeInsurance, DATENAME(MONTH, CreateDate) Month_name
    from CutomerPaymentDetail where [type] = @type
    group by categorytype, DATENAME(MONTH, CreateDate)
end
It not solved please post sample data and expected output .
Madan Shekar7y ago
please write a query like this
IF EXISTS(SELECT id  FROM   [dbo].[vtuser] WHERE  userid = '324')
print'if block works'
ELSE if exists (SELECT id  FROM   [dbo].[vtuser] WHERE  userid = '0' )
  PRINT 'else if block works'

  else
  print 'works else block '