Nested select statements

[malcster2]

SELECT tblNewTrans.Buyer, Count(tblNewTrans.Buyer) AS ["Total New Leads"], (select count(tblnewtrans.buyer) from tblnewtrans where tblnewtrans.new=true) AS Expr1, (select count(tblnewtrans.buyer) from tblnewtrans where tblnewtrans.refund=true) AS Expr2
FROM tblNewTrans
GROUP BY tblNewTrans.Buyer;


can anyone tell me why statement is working correctley. i have a table with a company field, paid field, refund field.

i need to run a query that gives me an amount to records for each company(that bit works), but also tell me how many payments have been recieve(new field) and how many payments have been refunded. all grouped under the correct company name. please help!!!!

the new and refund fields are checkboxes.

 

[ByteMyzer]

Maybe something like:

SELECT T1.Buyer, Count(T1.Buyer) AS ["Total New Leads"], T2.Expr1, T3.Expr2
FROM (tblNewTrans T1
INNER JOIN
 (SELECT T2.Buyer, COUNT(T2.Buyer) AS Expr1
  FROM tblNewTrans T2
  WHERE T2.new=TRUE
  GROUP BY T2.Buyer) T2 ON T1.Buyer=T2.Buyer)
INNER JOIN
 (SELECT T3.Buyer, COUNT(T3.Buyer) AS Expr2
  FROM tblNewTrans T3
  WHERE T3.refund=TRUE
  GROUP BY T3.Buyer) T3 ON T1.Buyer=T3.Buyer
GROUP BY T1.Buyer;

 

[malcster2]

thanks, but i got this message back - 'You tried to execute a query that does not include the specified expression 'Expr1' as part of an aggregate function'


any ideas?

 

[Jon K]

To use the nested select statements correctly, you need to use them in a correlated way by means of aliases.

SELECT tblNewTrans.Buyer, Count(tblNewTrans.Buyer) AS ["Total New Leads"],
(select count(tblnewtrans.buyer) from tblnewtrans as S1 where S1.new=true and S1.[Buyer]=tblNewTrans.[Buyer]) AS Expr1,
(select count(tblnewtrans.buyer) from tblnewtrans as S2 where S2.refund=true and S2.[Buyer]=tblNewTrans.[Buyer]) AS Expr2
FROM tblNewTrans
GROUP BY tblNewTrans.Buyer;


In fact you can achieve the same results in a simple Totals Query with two -Sum()s.

SELECT tblNewTrans.Buyer, Count(tblNewTrans.Buyer) AS ["Total New Leads"],
-Sum(tblNewTrans.new) AS NumberOfNew,
-Sum(tblNewTrans.refund) AS NumberOfRefund
FROM tblNewTrans
GROUP BY tblNewTrans.Buyer;

.