CODEWITHSUNDEEP
sql-servert-sql
Kevin Russell
Kevin Russell

Reputation: 45

Populate column based on "Select count(*) Group By"

I created a table in a stored procedure called @AccountStatRollup. I populated it with AccountIDs. I created a second table called @Contacts. I populated it with ContactIDs and ContactAccountIDs. I'm trying to populate the @AccountStatRollup.AccountContacts field with the total of related contacts from the @Contacts table.

I've tried a number if sql update statements and failed. This is the last statement I tried:

Thanks for any help.

Kevin

Declare @AccountStatRollup TABLE(
    AccountID varchar(30),
    AccountContacts int
)
Declare @Contacts TABLE(
    ContactID varchar(30),
    ContactsAccountID varchar(30)
)

My attempt to calculate Account Contacts count

UPDATE @AccountStatRollup a 
    SET a.AccountContacts = (SELECT COUNT(*)FROM @Contacts c GROUP BY c.ContactsAccountID)
    ON c.ContactsAccountID = a.ContactID

Thanks everyone! I managed to get this code to save and execute (Microsoft SQL server)

--Calculate Account Contacts count
    UPDATE @AccountStatRollup
    SET AccountContacts = (SELECT COUNT(*) FROM @Contacts 
    WHERE ContactsAccountID = AccountID )

I let the stored procedure run for a little while then I killed it. I'm realizing that this must be a very inefficient the way for me to count. I was going to use this example to add other column totals as well. I have maybe 30,000 accounts and 16,000 Contacts...

I tried to get this code working but it did not recognize c.ContactsAccountID, couldn't get around it.

--Calculate Account Contacts count ver 2      
    UPDATE a
    SET AccountContacts = c.cnt
    FROM @AccountStatRollup a join
         (SELECT COUNT(*) as cnt
          FROM @Contacts c
          GROUP BY c.ContactsAccountID
         ) c
         ON c.ContactsAccountID = a.AccountID;

Msg 207, Level 16, State 1, Procedure AccountStatRollup, Line 56 Invalid column name 'ContactsAccountID'.

Is there a more efficient way to calculate totals than what I tried?

Thanks!

Kevin

OK, I figured out how to make this work fast.

I created my temp tables with unique indexes.

-- Create a local table to hold Account Stats Declare @AccountStatRollup TABLE( AccountID varchar(18), AccountContacts int, UNIQUE (AccountID) );

-- Create a local table to hold Contact data Declare @Contacts TABLE( ContactID varchar(18), ContactsAccountID varchar(18), UNIQUE (ContactID) );

-- Create a local table to hold Contact calculation data Declare @tmpAccountCalculation TABLE( AccountID varchar(18), AccountName varchar(100), FieldCount int, UNIQUE (AccountID) );

-- Populate @AccountStatRollup table with all AccountID values
INSERT INTO @AccountStatRollup(AccountID) SELECT ID from sfAccount -- Populate @Contacts table with all AccountID values
INSERT INTO @Contacts(ContactID, ContactsAccountID) SELECT ID, ACCOUNTID from sfContact

-- Populate @tmpAccountCalculation table INSERT INTO @tmpAccountCalculation(AccountID, AccountName, FieldCount) SELECT dbo.sfAccount.ID, dbo.sfAccount.NAME, COUNT(dbo.sfContact.ACCOUNTID) AS ContactCount FROM dbo.sfAccount INNER JOIN dbo.sfContact ON dbo.sfAccount.ID = dbo.sfContact.ACCOUNTID GROUP BY dbo.sfAccount.ID, dbo.sfAccount.NAME HAVING (COUNT(dbo.sfContact.ACCOUNTID) > 0)

-- Update the @AccountStatRollup table UPDATE A SET A.AccountContacts = B.FieldCount FROM @AccountStatRollup A INNER JOIN @tmpAccountCalculation B ON A.AccountID = B.AccountID

-- Test the output Select * from @AccountStatRollup where AccountContacts >0 Order by AccountContacts desc

This takes about 1 second to run.

Whew....

Kevin

Upvotes: 0

Views: 733

Answers (2)

Lajos Veres
Lajos Veres

Reputation: 13725

You should move the where condition to the inner query, and you don't need the group by:

UPDATE @AccountStatRollup a 
  SET
    a.AccountContacts = (
      SELECT
        COUNT(*)
      FROM
        @Contacts c
      where
        c.ContactsAccountID = a.ContactID
  );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Because you are using table variables, I'm assuming that this is SQL Server. You can do the update in a nifty way using update with join syntax:

UPDATE a
    SET AccountContacts = c.cnt
    FROM @AccountStatRollup a join
         (SELECT COUNT(*) as cnt
          FROM @Contacts c
          GROUP BY c.ContactsAccountID
         ) c
         ON c.ContactsAccountID = a.ContactID;

The SQL standard way, which should work in almost all databases, is to use a correlated subquery:

UPDATE @AccountStatRollup a 
    SET AccountContacts = (SELECT COUNT(*)
                           FROM @Contacts c
                           WHERE c.ContactsAccountID = a.ContactID
                          );

When you use the correlated subquery, the group by is not needed. In fact, including a group by is misleading, because it suggests that there might be more than one group -- and that would cause an error when the statement is executed.

Upvotes: 0

Related Questions