sql server group by, count, compute

Question

Query:

 SELECT purchased_profileid, 
        COUNT(distinct(purchased_profileid)) as 'NoofPurchases', 
        final_paidprice  
   FROM MMBMembership mmb, 
        MMB_BusinessProfiles mmbProfiles, 
        basic_details bd, 
        orderdetails_purchasedProfiles odpp
   WHERE mmb.mmb_id = mmbProfiles.MMB_id
     AND mmb.Profile_id = LTRIM(bd.profile_id)
     AND odpp.purchased_profileid = bd.profile_id
     AND mmb.mmb_id = 1
     AND ispublished = 'true'
GROUP BY final_paidprice, purchased_profileid

Result:

purchased_profileid  NoofPurchases  final_paidprice
------------------------------------------------------
10                   1              314.828012285208
10                   1              788.635686407162
11                   1              926.007854252126
11                   1              1000
11                   1              1385.59720636606

How should I modify this query to give me the output as

purchased_profileid  NoofPurchases  final_paidprice
----------------------------------------------------
10                   2              1103.462
11                   3              3311.597

dcp · Accepted Answer · 2011-07-06 15:46:52Z

3

select purchased_profileid, count(*) as 'NoofPurchases', sum(final_paidprice)  from MMBMembership mmb, MMB_BusinessProfiles mmbProfiles, basic_details bd, orderdetails_purchasedProfiles odpp
where mmb.mmb_id = mmbProfiles.MMB_id
and mmb.Profile_id = ltrim(bd.profile_id)
and odpp.purchased_profileid = bd.profile_id
and mmb.mmb_id = 1
and ispublished='true'
group by  purchased_profileid

answered Jul 6, 2011 at 15:46

dcp

55.7k24 gold badges149 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sun Over a year ago

@Scorpio0: it is giving me a "Incorrect syntax near the keyword 'group'. pointing towards the last line "group by purchased_profileid"

dcp Over a year ago

@Sun - I'm not sure why you would be getting that. Did you original query run ok? I'd have to see the table definitions for all the tables used in your query to be able to determine what the problem could be.

Collectives™ on Stack Overflow

sql server group by, count, compute

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related