hello, i'm just wondering if the following is possible - i would probably use sub-queries if i wasn't using llblgen!

my database diagram is as follows:

and basically this is a mailshot facility. I want to create a table with the following columns:

for each mailshot:

DateCreated, SysName, Count(total recipients in mailshot), Count(total recipients where read is true), Count(total recipients where link is attached to mailshot)

can anyone help me please!

thank you, Mike

Mike,

The LLBLGen Pro User Manual has a page titled:

Generated code - Field expressions and aggregates

It will give you all you need to fashion your query.

Thanks

I know how to do aggregate queries, it's more that there are two counts there with different conditions on them. I have been trying to formulate an expression for this but it seems 'double-count' the rows. I am probably being a SQL dunce!

Counts are per group in your group by, perhaps the count for one field requires different fields (another group) than the other count?

Mike, It looks like subqueries will be needed to get the individual counts.

Change:

mikeydelamonde wrote:

DateCreated, SysName, Count(total recipients in mailshot), Count(total recipients where read is true), Count(total recipients where link is attached to mailshot)

To something more like this:

DateCreated, 
SysName, 
Cnt1 = (Select Count(recipients) From mailshot), 
Cnt2 = (Select Count(recipients) From mailshot where read=true), 
Cnt3 = (Select Count(recipients) From mailshot where IsNull(link)=False)

I had to guess at some of your table attributes, so my query will probably need to be modified. Hopefully this helps.