anyone see where i messed up this query?

[lmp101010]

SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click], [US Brown.Unique Click], [US Gold.Unique Click] [US GameClub.Unique Click]) AS sum[SumOfUnique click1]
FROM [US Brown], [US GameClub], [US Gold], [US Platinum]
GROUP BY [US Platinum].[Mailing Date];

This is a query written from other queries. I attached an example of the "Platinum" query. Each one I am using has the same colums. I am trying to get the sum of each catagory (US Brown, Gold Platinum etc.) and create a new aggregate query.

Getting an error with the Select satement..Thanks

 

[pbaldy]

You have the Sum() around all 4 fields; you need one around each.

 

[lmp101010]

Thanks for the reply. I did it again as you suggested.

SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click], Sum[US Brown.Unique Click], Sum[US Gold.Unique Click] Sum[US GameClub.Unique Click]) AS sum[SumOfUnique click1]
FROM [US Brown], [US GameClub], [US Gold], [US Platinum]
GROUP BY [US Platinum].[Mailing Date];

Still getting a syntax/missing operator error.

 

[lmp101010]

I thought it might have something to do with the parentheses, tried a few variations with them including this:

SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click]), Sum([US Brown.Unique Click]), Sum([US Gold.Unique Click]) Sum([US GameClub.Unique Click]) AS sum([SumOfUnique click1])
FROM [US Brown], [US GameClub], [US Gold], [US Platinum]
GROUP BY [US Platinum].[Mailing Date];

For some reason I am still getting the same results.

 

[pbaldy]

I am? You'll also need to drop the sum here:

As sum[SumOfUnique click1]

 

[lmp101010]

still getting that same error with these..

SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click]), Sum([US Brown].[Unique Click]), Sum([US Gold].[Unique Click]) Sum([US GameClub].[Unique Click]) AS [SumOfUnique click1]
FROM [US Brown], [US GameClub], [US Gold], [US Platinum]
GROUP BY [US Platinum].[Mailing Date];

thanks for the help all.

 

[pbaldy]

You're missing a comma. Try this:

SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click]) AS Platinum, Sum([US Brown].[Unique Click]) AS Brown, Sum([US Gold].[Unique Click]) AS Gold, Sum([US GameClub].[Unique Click]) AS GameClub
FROM [US Brown], [US GameClub], [US Gold], [US Platinum]
GROUP BY [US Platinum].[Mailing Date]

I would expect joins between those tables, or you're going to get a Cartesian product.

 

[lmp101010]

That did it as far as the error. Thanks.

The results are still not what i need though. My logic must be off. Attached is what I would ultimately like to have. My query is only dealing with clicks but I'm just trying to keep it simple until I figure things out and can alter.

Anyhow it still giving me the sum of each instead of aggregating the results for each column and grouping by day. It seems the select statement needs altering so that I select sum(all queries.clicks) instead of each individually. But I don't know that there is a way to do that. Or maybe I'm wrong.

 

[pbaldy]

Ah; try

SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click]) + Sum([US Brown].[Unique Click]) + Sum([US Gold].[Unique Click]) + Sum([US GameClub].[Unique Click]) AS AggregateClicks
FROM [US Brown], [US GameClub], [US Gold], [US Platinum]
GROUP BY [US Platinum].[Mailing Date]

 

[ByteMyzer]

Would it be too much to ask if you could supply your table structures? Example:

US Platinum
----------
Mailing Date
Unique click

US Brown
----------
Mailing Date
Unique click

 

[pbaldy]

Come to think of it, you could probably do this too:

Sum([US Platinum].[Unique click] + [US Brown].[Unique Click] + [US Gold].[Unique Click] + [US GameClub].[Unique Click]) AS AggregateClicks

 

[lmp101010]

That makes sense. Thanks for writing that code. Do you happen to see the syntax error in this from statement?

FROM ([US GameClub] INNER JOIN [US Gold] ON [US GameClub].[Mailing Name]=[US Gold].[Mailing Name]) INNER JOIN [US Platinum] ON [US Gold].[Mailing Name]=[US Platinum].[Mailing Name]

 

[lmp101010]

Attached the table design..Also, the issue with the from statement seems to be resolved with the second query from paul. However, i get zero results. The attached is the test data i'm using so i did expect to see something.

 

[pbaldy]

Can you post the db? I don't understand how the tables relate to each other. I wonder if they should be unioned rather than joined?

 

[lmp101010]

I think the file might be too big for this forum..

 

[lmp101010]

Hi Paul,

I zipped up this test db so it would fit. Maybe you can enlighten me as to why these queries are not updating with new data in the tables.

 

[pbaldy]

Can you describe what the queries contain, in general terms? You are joining on the mailing name, but there are none in common, which is why you get no results. My gut is these need to be brought together with a UNION query, not joined. Or just aggregated from the raw tables.

 

[lmp101010]

The queries contain the result of email marketing campaigns. You're right the mail names are all unique. I'll try a union instead. That makes sense with the aggregate results. However, with the gold, platinum etc. queries would that be the same issue? You'll notice that those do have some results (although they cut off after a certain date inexplicably), but they are also joined by a unique identifier in the serial column. Thanks again for looking at these.

 

[pbaldy]

My guess would be it "cuts off" due to the joins and the nature of the data. I notice that some mailings do not have records in all 4 tables. What exactly do you expect this query to show you?

 

[lmp101010]

Your right, the test records were different in some of the table so I corrected that and now those queries are working. So happy about that...thanks a million. As to the aggregate query...do you think I could fix that by simply changing the INNER JOIN to UNION?