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

SQL Query Question

P: n/a
Hi all,

I am trying to achieve the following:

Table A has a structure like this:

TransactionNumber (autonumber PK)
Quantity
ProductTypeID (let's say this can have the value 1,2, or 3)
Date

I want to query this data to produce a data structure where each row
has a date (I will get this from a temp table of all dates in my
desired range), then the SUM of any rows containing that date, with
each of the three product types being a column.

So an example row in the resultset would look like:

April 1, 2006;877;155;5

...calculated from the database rows:

188;577;1;April 1, 2006
194;155;2;April 1, 2006
199;5;3;April 1, 2006
204;300;1;April 1, 2006

I do have a solution but it is not very elegant and I am wondering what
others may make of the problem. Is it possible to do this using SQL
only?

Apr 12 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
SELECT Date,
SUM(CASE WHEN ProductTypeID=1 THEN Quantity ELSE 0 END) AS Type1,
SUM(CASE WHEN ProductTypeID=2 THEN Quantity ELSE 0 END) AS Type2,
SUM(CASE WHEN ProductTypeID=3 THEN Quantity ELSE 0 END) AS Type3
FROM TableA
GROUP BY Date

Apr 12 '06 #2

P: n/a
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

You talk about "autonumbers", which are non-relational. You use
non-ISO-8601 date format in your vague narratives.. You have data
element names like "foobar_type_id" -- is it a type or an identifier,
since it CANNOT EVER BE BOTH!!! Do you know that DATE is both too
vague and a reserved word??

Let's assume that you are selling squid on line Maybe you meant this?

CREATE TABLE SquidSales
(squid_wgt DECIMAL (10,2) NOT NULL
CHECK (squid_wgt > 0.0),
squid_size INTEGER DEFAULT 1 NOT NULL
CHECK (squid-size IN (1,2, 3)),
stock_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL PRIMARY KEY);;
I want to query this data to produce a data structure where each row has a date (I will get this from a temp table of all dates in my desired range [Google "Calendar Table"] ), then the SUM of any rows containing that date, with each of the three product types being a column. <<


Why are you using temp tables?

SELECT stock_date,
SUM(CASE WHEN squid_size = 1 THEN squid_wgt ELSE 0.0 END) AS
size_1,
SUM(CASE WHEN squid_size = 2 THEN squid_wgt ELSE 0.0 END) AS size_2
,
SUM(CASE WHEN squid_size = 3 THEN squid_wgt
ELSE 0.0 END) AS size_3]
FROM SquidSale
GROUP BY stock_date,;

Apr 12 '06 #3

P: n/a
Thanks, those suggestions worked fine - just needed a refresher as I've
been mostly working in Java and very little SQL for the past two years.
I do know better than to use reserved words and non-standard dates. 8^)

Apr 13 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.