CODEWITHSUNDEEP
sqlsql-servergroup-by
Manjay_TBAG
Manjay_TBAG

Reputation: 2245

Error if use subquery in group by : Cannot perform an aggregate function on an expression containing an aggregate or a subquery

I have one to one mapping in two tables. For each order there is a purchase, but sometimes purchase are bad so remove association in order table. But I need an aggregate table grouped by seller , for which seller how many bad purchases and valid purchases are there.

Examples 

     Order Table
OrderId     PurchaseId                 
--------    -----------     
  o1            p1
  o2            p6
  o3            null
  o4            p2
  o5            p3



Purchase Table
PurchaseId      OrderId     SellerId
-----------     ---------   ---------
p1              o1          s1
p2              o4          s1
p3              o5          s2
p4              o2          s2
p5              o3          s3
p6              o2          s4

Aggregare Seller Table
SellerId        NullPurchase       validPurchase       TotalPurchase
---------       -------------      --------------      --------------
s1              0                       2               2
s2              1                       1               2
s3              1                       0               1
s4              0                       1               1

Query to get aggregate table

Select SellerId, count(*) as TotalPurchase,
count(case when ((Select count(*) from dbo.Order where purchaseId = p.PurchaseId) = 0) then 1 end) as NullPurchase
count(case when ((Select count(*) from dbo.Order where purchaseId = p.PurchaseId) = 1) then 1 end) as validPurchase
from dbo.Purchase p
left join on dbo.Order o
on p.OrderId = o.OrderId
group by sellerId

Upvotes: 0

Views: 719

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You don't need subqueries:

select p.SellerId, count(*) as TotalPurchase,
       count(*) - count(o.OrderId) as NullPurchase,
       count(o.OrderId) as validPurchase
from dbo.Purchase p left join 
     dbo.Order o
     on p.OrderId = o.OrderId
group by p.sellerId

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

I think this is one way to do what you want, if I understand your goal correctly:

Select SellerId, count(*) as TotalPurchase,
sum(case when o.purchaseId IS NULL then 1 end) as NullPurchase,
sum(case when o.purchaseId IS NOT NULL then 1 end) as validPurchase
from dbo.Purchase p
left join on dbo.Order o
  on p.OrderId = o.OrderId
  AND p.purchaseId = o.purchaseId
group by sellerId

Upvotes: 1

Related Questions