Select Count...for multiple columns

[LNBruno]

Ok - this is totally in left field:

I've got a table (inherited) that has, as columns, a series of attributes. I need to get a count of each occurrence of each of the attributes. I can, of course, write 165 queries that say:

SELECT attribute, count(attribute)
FROM tblFromH3ll
GROUP BY attribute;

...but I'd rather find something more elegant that will allow me to get a count of all the columns in the table in one pass...

Am I smoking crack?



< M!ke >

 

[JoeSmoe55]

Can you be a little more specific? Do you have a table with 165 columns? If so I would probably redesign the table however that doesn't solve your problem. I might be dense but I need you to be more specific on the table layout.

The other Mike

 

[LNBruno]

Yes, the table has 165 columns. Redesigning is not an option as the data is coming from an external source.

So I have a table with a structure like:

keyID
Attribute 1
Attrubute 2
...snip...
Attribute 165

And I need to get a count on each column.

Make sense?

< M!ke >

 

[lespaul]

if the table is coming from an external source, what's to stop you from importing it into YOUR structure as a normalized table?

SELECT KeyID, 'Attribute1' As Attribute From TableName WHERE Attribute1
UNION
SELECT KeyID, 'Attribute2' From TableName WHERE Attribute2
UNION
SELECT KeyID, 'Attribute3' From TableName WHERE Attribute3
etc.

now use this query as the source of the query you really want:

SELECT attribute, count(attribute)
FROM tblFromH3ll
GROUP BY attribute;




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[LNBruno]

Hmmm... 165 unions?

I think this is getting me closer to where I want to be:

    Dim adoConn As Connection
    Dim fld As Field
    Dim rst As ADODB.Recordset
    Set adoConn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    rst.Open "tblFromH3ll", adoConn
    For Each fld In rst.Fields
        Debug.Print fld.Name
        '// and build the SQL statement using fld.Name???
    Next fld

"I never took the Kobayashi Maru test until now. What do you think of my solution?"
-- Spock (Star Trek: Wrath of Kahn)


< M!ke >

 

[lespaul]

well you only need 165 UNIONS if you don't normalize when you get the external data.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[JoeSmoe55]

you could put the column names in a table and programmically build the union and then run the count.

 

[JerryKlmns]

This is how I treat my flat file with 6 sets of collaterals for loan accounts

For intCount = 1 To 6
CurrentProject.Connection.Execute "INSERT INTO All_5_1 ( Ti, Bank, Center, Main, Sec_ID, AA, Sec_Amount, Safe_Amount, HouseCode, MarketValue, BankValue, HouseType, HouseKind ) " & _
"SELECT Ti, Bank, Center, Main, Sec_ID" & intCount & ", Sec_Order" & intCount & ", Sec_Amount" & intCount & ", Safe_Amount" & intCount & ", HouseCode" & intCount & ", MarketValue" & intCount & ", BankValue" & intCount & ", HouseType" & intCount & ", HouseKind" & intCount & " " & _
"FROM Sbc1 WHERE ((Sec_ID" & intCount & ") Is Not Null);", adExecuteNoRecords, adCmdText
Next intCount

the fields Sec_ID, AA, Sec_Amount, Safe_Amount, HouseCode, MarketValue, BankValue, HouseType, HouseKind are followed by a number from 1 to 6.
Then the table containing all collaterals for loans is managable...

 

[LNBruno]

Thanks for suffering along with me. Time required a solution regardless of how elegant. I ended up with something like this (abbreviated version):

    Set adoConn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "tblFromH3ll", adoConn

    For Each fld In rst.Fields
        sSQL = "SELECT '" & fld.Name & "' AS Attribute, " _
            & vbCrLf & "H." & fld.Name & " AS ReturnValue, " _
            & vbCrLf & "Count(H." & fld.Name & ") As FreqCount " _
            & vbCrLf & "FROM tblFromH3ll AS H " _
            & vbCrLf & "GROUP BY H." & fld.Name & ";"
        DoCmd.RunSQL (sSQL)
    Next fld

< M!ke >