Hi,

It's a little off-topic, because it's not about Pascal or Lazarus, but it will be used in a Lazarus application, so I dare to ask here ;-). I have a table with data, using Firebird. I want to have some statistics so I use this query :
select a.TYPE, COUNT(*)
from Table a
WHERE a.date between '1.1.2008' and '31.12.2008'
GROUP by a.TYPE

I also want it for the next year :

select a.TYPE, COUNT(*)
from Table a
WHERE a.date between '1.1.2009' and '31.12.2009'
GROUP by a.TYPE

This way I get two dbgrids of data, or two graphs. I would like to have 1 grid, with three columns : type, count for 2008 and count for 2009, to compare them and to graph them next to eachother. That's where I'm stuck.

Anyone has suggestions ?
I googled for pivottable or cross tab, but I didn't find how to create the data from a database.
I have a rather good textbook about databases (using Interbase !) but I didn't find anything giving me usefull idea's.
I tried with a union of the both statements, but that does not create an extra column of data, it "mixes" the two datasets into one column. I tried grouping by date, but that gives an extra column with only years.

Thanks,

Koenraad Lelong.

If you really want three columns with counts for the two hardcoded years, you could use subqueries:

with distinct_types (

  type

)
as (
select distinct type from table
)
select
dt.type,
(
select
count(*) count_2008
from table
where (type = dt.type) and (extract(year from date) = 2008) ),
(
select
count(*) count_2009
from table
where (type = dt.type) and (extract(year from date) = 2009) )
from distinct_types dt;

but perhaps the following is better since it's generic for all years (and if needed you can still limit the years using a HAVING clause):

select
a.type,
extract(year from a.date) a_year,
count(*)
from table a
group by 1, 2;

Hi,

First, apologies for creating a new thread. I have to reply after reading the archive. I didn't get any messages via mail from the list. I'll have to check my filters, but I very much doubt the messages are discarded.

Anyway, thanks, Ondrej, for your reply.
Are you sure the first query runs on Firebird 2.0 ? Your second suggestion runs, but it's not really what I want, unless I'm missing something.
What I did to have something is run the queries I posted and imported the data in a spreadsheet and manually ordered it : TYPE 2008 2009
209/00405/00 11 5
209/00405/03 1 0
209/00438/50 1 1
209/00440/00 50 31
209/00440/00R 1 2
209/00440/20 35 14
209/00440/70 65 21
209/00440/70R 1 2
209/00516/00 1 0
209/00547/00 134 38
209/00547/40 1 11
209/00550/00 1 0
209/00577/00 14 8
209/00580/00 177 11
etc.
This way you can see the differences between years. I think this can give a nice graph also.
When grouping by year (your second query) you get this : TYPE YEAR COUNT
209/00405/00 2007 10
209/00405/00 2008 11
209/00405/00 2009 5
209/00405/03 2008 1
209/00438/50 2007 4
209/00438/50 2008 1
209/00438/50 2009 1
209/00440/00 2007 13
209/00440/00 2008 50
209/00440/00 2009 31
209/00440/00R 2008 1
209/00440/00R 2009 2
209/00440/20 2008 35
209/00440/20 2009 14
209/00440/20 2010 10
209/00440/70 2007 52
209/00440/70 2008 65
209/00440/70 2009 21
209/00440/70R 2008 1
209/00440/70R 2009 2
209/00516/00 2007 2
209/00516/00 2008 1
209/00547/00 2007 37
209/00547/00 2008 134
209/00547/00 2009 38
209/00547/40 2008 1
209/00547/40 2009 11
209/00547/40 2010 3
209/00550/00 2008 1
209/00577/00 2008 14
209/00577/00 2009 8
etc.
One can see the differences by year, but a manager will curse the programmer that gives him this to analyse. And graphing this will not clarify this.
Of course, I could be missing something.

Thanks anyway.

Koenraad Lelong.

Hello Lazarus-List,

Monday, February 8, 2010, 10:48:34 AM, you wrote:

KL> select a.TYPE, COUNT(*)
KL> from Table a
KL> WHERE a.date between '1.1.2009' and '31.12.2009' KL> GROUP by a.TYPE
KL> This way I get two dbgrids of data, or two graphs. KL> I would like to have 1 grid, with three columns : type, count for 2008 KL> and count for 2009, to compare them and to graph them next to eachother. KL> That's where I'm stuck.

In this cases as data is to be shown or exported I'm simply creating a MemDS with the data from the 2, 3, ...n queries and using the result MemDS as the source for the Grid.

On 8 February 2010 12:29, Koenraad Lelong
wrote:

[snip]

Are you sure the first query runs on Firebird 2.0 ?

No, AFAIK, Firebird only supports common table expressions since 2.1. Here's the SQL statement without the CTE:

select
a.type,
(
select
count(*) count_2008
from table
where (type = a.type) and (extract(year from date) = 2008) ),
(
select
count(*) count_2009
from table
where (type = a.type) and (extract(year from date) = 2009) )
from table a;

Do you need the subqueries?

select
a.type,
sum( if(extract(year from date) = 2008, 1, 0) ) c2008, sum( if(extract(year from date) = 2009, 1, 0) ) c2009 from Table a
WHERE a.date between '1.1.2008' and '31.12.2009';

On 8 February 2010 14:20, Martin wrote:
[snip]

No, of course not. Your suggestion should work fine, too. I would suspect that there might be a slight performance hit, depending on the size of the table and existing indexes - unless the Firebird optimizer is much smarter now. ;-) Probably the best is to try them both and choose the one which performs better.