SQL Query Question

[commerce@allenwhite.net]

Hi all,

I am trying to achieve the following:

Table A has a structure like this:

TransactionNumb er (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?

[markc600@hotmail.com]

Re: SQL Query Question

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

[--CELKO--]

Re: SQL Query Question

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 "autonumber s", 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_TIMESTA MP NOT NULL PRIMARY KEY);;
[color=blue][color=green]
>> 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. <<[/color][/color]

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,;

[commerce@allenwhite.net]

Re: SQL Query Question

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^)