can someone help me with this SQL query?

[richard]

I have a table of raw sales data which I want to query, so the output can
be imported into Excel and charted. The raw data looks like this, with
one record for each sale:

table 'data'

date size
1/6/04 medium
1/5/04 small
1/6/04 medium
2/6/04 large
3/6/04 medium
3/6/04 large
etc.

What I want to chart is the number of sales for each size on a daily
basis, i.e.

date small medium large
1/6/04 1 2 0
2/6/04 0 0 1
3/6/04 0 1 1

I succeeded in writing a query which can give me the data for one size,
i.e.

SELECT data.DATE, Count (SIZE) AS size total
FROM data
WHERE size = 'small'
GROUP BY data.DATETIME;

which gives me

date small
1/6/04 1
2/6/04 0
3/6/04 0

But I am unable to extend the query to give me all 3 size summaries in
one table.

Any help appreciated.

~richard

[JHB]

Hi Richard
TRANSFORM Count(data.Size) AS AntalOfSize
SELECT data.Date
FROM data
GROUP BY data.Date
PIVOT data.Size;

Regards
Jørn
"richard" <no****@myplace.net> skrev i en meddelelse
news:z6*****************@news-server.bigpond.net.au...

I have a table of raw sales data which I want to query, so the output can
be imported into Excel and charted. The raw data looks like this, with
one record for each sale:

table 'data'

date size
1/6/04 medium
1/5/04 small
1/6/04 medium
2/6/04 large
3/6/04 medium
3/6/04 large
etc.

What I want to chart is the number of sales for each size on a daily
basis, i.e.

date small medium large
1/6/04 1 2 0
2/6/04 0 0 1
3/6/04 0 1 1

I succeeded in writing a query which can give me the data for one size,
i.e.

SELECT data.DATE, Count (SIZE) AS size total
FROM data
WHERE size = 'small'
GROUP BY data.DATETIME;

which gives me

date small
1/6/04 1
2/6/04 0
3/6/04 0

But I am unable to extend the query to give me all 3 size summaries in
one table.

Any help appreciated.

~richard