General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Gathering info from multiple records into 1 row
Hi Everyone,
Lets assume for a moment that you have a table with: key (10 digit number), acctount_type (ATM, CHECKING, SAVINGS ECT.), and months (Number of months the account has been open). We want to write an SQL statement that will give us results that look like this:
KEY,
CNT-KEY(number of records with that same key),
cnt-account_Type(number of unique account _types within the groups of same keys)
MAX(month),
accout_type (that goes with MAX(months)),
MIN(Month),
account_type (that goes with the minium month record)
The code below does almost what we want but we can not get it to show the account_type for the min and max months records on the same row. Please help if you can.
(The table name is SHAY and the account_type field is called ptype)
SELECT Shay.key, COUNT(*), COUNT(distinct ptype), max(months), min(months);
FROM shay;
GROUP BY Shay.key
Thanks in adavance :)
Carl Burkhart
carl.burkhart@brannrmg-va.com
BrannData
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only