By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,996 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,996 IT Pros & Developers. It's quick & easy.

can someone help me with this SQL query?

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.