SQL

[sriky]

I have data in Access database. Sample data is:



Timestamp Count Abort
09/09/99 23 34
09/10/99 34 12
09/11/99 12 65
09/12/99 56 42
10/01/99 43 12
10/02/99 43 54





I would like this data to appear as follows:



Timestamp Abort Count
Sept 1999 186 201
Oct 1999 89 102





So, I want this data to be displayed in monthly intervals. Can someone let me know how?

Please

[Lothar Haensler]

select month(timestamp), sum(abort), sum(count) from tablename group by month(timestamp)

[sriky]

if I use 'group by timestamp', it is grouping the data for each timestamp (I have several item ID's for each timestamp). But I want data to be grouped for all timestamps. How can I do that?

Thanks

[Lothar Haensler]

I didn't write anything about "group by timestamp" but about "group by month(timestamp). I assumed that you wanted to group by month, was I wrong?

[sriky]

Hi Lothar,

I tried to group by month, but that does'nt work.

Thanks

[Lothar Haensler]

that's funny, because the code I posted was copied and pasted from an Access database Query window. So, I can guarantee, that it did work.
Could you post the SQL of your query that doesn't work?

[sriky]

SELECT format(timestamp,"mmm yyyy"), sum(count), sum(f100tme), sum(abort)
FROM cmlap100
GROUP BY month(timestamp)
ORDER BY timestamp;





Thanks

[Lothar Haensler]

I see, the expression specified in the GROUP BY clause must also occur in the SELECT list.
Thus write it like this:
SELECT format(timestamp,"mmm yyyy"), ...
GROUP BY format(timestamp,"mmm yyyy")
...

I'm not sure but "GROUP BY 1" might also work in Access (1 stands as a placeholder for the first expression in the select list).