CODEWITHSUNDEEP
sqlsql-serverazure-sql-database
Abe Miessler
Abe Miessler

Reputation: 85056

How to aggregate data across multiple columns?

I have a table containing raw data that looks like so:

geoid  business
-------------------
1      BestBuy
1      WalMart
1      Target
2      BestBuy
2      Target
2      BestBuy
3      WalMart

These businesses map to columns in another table like so:

BestBuy -> BigBox_1
Walmart -> BigBox_2
Target  -> BigBox_3

I'm trying to find a clean way to query the raw data table and populate the destination table which should represent the same data like so:

geoid BigBox_1_Count  BigBox_2_Count   BigBox_3_Count
----------------------------------------------------------
1    |            1  |              1 |                1
2    |            2  |              0 |                1
3    |            0  |              0 |                1

I realize that I can do this with three different queries like so:

INSERT INTO destTable (geoid, BigBox_1_Count)
SELECT geoid, COUNT(*) WHERE business = 'BestBuy'

and then update statement for the other two.

But I'm wondering if there is a way that I could insert all of the counts to the appropriate column with a single insert statement? Can this be done?

Note - I realize this table structure is not ideal. I didn't create it and redesigning it is on my list of things to do but for now I need to work with what I have.

Upvotes: 0

Views: 223

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would do aggregation :

INSERT INTO destTable (geoid, BigBox_1_Count, . . . )
     SELECT geoid, 
            SUM(CASE WHEN business = 'BestBuy' THEN 1 ELSE 0 END) AS BigBox_1_Count,
            SUM(CASE WHEN business = 'WalMart' THEN 1 ELSE 0 END) AS BigBox_2_Count,
            SUM(CASE WHEN business = 'Target' THEN 1 ELSE 0 END) AS BigBox_3_Count
     FROM table t
     GROUP BY geoid;

Upvotes: 2

Related Questions