Group By

[Mike]

I have a data table which consists of the following fields:
concatenate1(which concatenates my gvkey, & datadate), gvkey, datadate, tic,
sic, pbaco, etc....Each field for a given concatenate number is either blank,
or has some data in it across the entire data set. I would like to bring back
one row, signified by the concatenate1 field with one line of data. Example
is shown below. Thanks in advance.

Concatenate 1 gvkey datadate tic sic
pbaco
1000-20081503 1000 20081503 5
1000-20081503 1000 20081503 6
1000-20081503 1000 20081503
7

Need it to be:
Concatenate1 gvkey datadate tic sic
pbaco
1000-20081503 1000 20081503 5 6
7

 

[Stefan Hoffmann]

hi Mike,

I have a data table which consists of the following fields:
concatenate1(which concatenates my gvkey,& datadate), gvkey, datadate, tic,
sic, pbaco, etc....Each field for a given concatenate number is either blank,
or has some data in it across the entire data set. I would like to bring back
one row, signified by the concatenate1 field with one line of data. Example
is shown below. Thanks in advance.

Concatenate 1 gvkey datadate tic sic
pbaco
1000-20081503 1000 20081503 5
1000-20081503 1000 20081503 6
1000-20081503 1000 20081503
7

Need it to be:
Concatenate1 gvkey datadate tic sic
pbaco
1000-20081503 1000 20081503 5 6
7

In your simply case you only need to group your query - press the Greek
S in the toolbar - and select Maximum as aggregate function for the
fields tic, sic, pbaco.

So that your query's SQL looks like

SELECT Concatenate1, gvkey, datadate, Max(tic), Max(sic), Max(pbaco)
FROM yourTable
GROUP BY Concatenate1, gvkey, datadate;

mfG
--> stefan <--