Thank you very much for the SQL statement. It really does the job. I only
added an order by year to the outer query, but the job was already well
done. Here is the query:
SELECT to_char(date_trunc('year', t1.ts), 'YYYY') AS year, count(*) FROM
(SELECT to_timestamp(text_value, 'YYYY-MM-DD') AS ts FROM metadatavalue
WHERE metadata_field_id = 12) t1 GROUP BY date_trunc('year', t1.ts) order by
year;
Thnaks for saving my dear.
From: George Hamilton [mailto:[email protected]]
Sent: Tuesday, October 12, 2010 6:51 PM
To: [email protected]
Subject: Re: [Dspace-tech] Annual submission Rates
Hi
The following should work (on Postgres):
SELECT to_char(date_trunc('year', t1.ts), 'YYYY') AS year, count(*) FROM
(SELECT to_timestamp(text_value, 'YYYY-MM-DD') AS ts FROM metadatavalue
WHERE metadata_field_id = 12) t1 GROUP BY date_trunc('year', t1.ts);
George
On 12/10/10 16:47, Admire wrote:
Hi All,
Hope I find you well. I am interested in establishing the number of items
deposited each year in our IR. Is there anyone out there who had done that
and can provide me with the SQL statement to do that.
Regards,
Admire
----------------------------------------------------------------------------
--
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
Spend less time writing and rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
_______________________________________________
DSpace-tech mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
--
This message has been scanned for viruses and
dangerous content by <http://www.mailscanner.info/> MailScanner, and is
believed to be clean.
------------------------------------------------------------------------------
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
Spend less time writing and rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
_______________________________________________
DSpace-tech mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
