locked
Aggregate and group by queries RRS feed

  • Question

  • I'm looking to do something similar to the below query, but using query expressions or fetchxml.
      SELECT cc.new_name, cc.new_classid, COUNT(1)
        FROM test_MSCRM.dbo.ContactExtensionBase cb 
        join test_MSCRM.dbo.new_class cc on cc.new_classId = cb.new_classid
    GROUP BY cc.new_name, cc.new_classid
    The issues I have with the solution below are:
    1. I need multiple queries to handle all the views (active, inactive, my active, etc)
    2. I'm accessing the database directly for reading
    Is there any way to do aggregate + grouping through the supported methods (query expressions/fetchxml)

    The only solution we've come up with really is. 

    fetch all class types
    foreach(classtype)
      fetch count of contacts with this class type

    Any help would be greatly appreciated
    Thursday, January 21, 2010 7:25 PM

Answers

  • Very nice!  It says they were added by rollup 2.  However, I notice there is no group by.  It appears that all these queries return one row-- a single count. I'll paste the examples here for future viewers:

    CRM Fetch XML

    SQL

    <attribute name='address1_city' aggregate='count' alias='citycount' />

    SELECT COUNT(*) AS 'citycount' FROM Account

    <attribute name='address1_city' aggregate='countcolumn' alias='citycount' />

    SELECT COUNT(address1_city) AS 'citycount' FROM Account

    <attribute name='address1_city' aggregate='countcolumn' alias='citycount' distinct='true' />

    SELECT COUNT(DISTINCT address1_city) AS 'citycount' FROM Account


    Friday, January 22, 2010 3:13 PM

All replies

  • I don't think you can aggregate with query expressions or fetchxml.  However, assuming you have a work around by using SQL:
    1) you would need multiple or parameterized queries regardless of platform
    2) it is supported to query the CRM filtered views directly

    SELECT [address1_postalcode]
          ,[new_accounttype]
          ,count(*)
      FROM [test_MSCRM].[dbo].[FilteredContact]
     GROUP BY [address1_postalcode]
          ,[new_accounttype]
    GO
    
    
    Thursday, January 21, 2010 8:20 PM
  • Very nice!  It says they were added by rollup 2.  However, I notice there is no group by.  It appears that all these queries return one row-- a single count. I'll paste the examples here for future viewers:

    CRM Fetch XML

    SQL

    <attribute name='address1_city' aggregate='count' alias='citycount' />

    SELECT COUNT(*) AS 'citycount' FROM Account

    <attribute name='address1_city' aggregate='countcolumn' alias='citycount' />

    SELECT COUNT(address1_city) AS 'citycount' FROM Account

    <attribute name='address1_city' aggregate='countcolumn' alias='citycount' distinct='true' />

    SELECT COUNT(DISTINCT address1_city) AS 'citycount' FROM Account


    Friday, January 22, 2010 3:13 PM
  • Yes, I would improve these examples by changing the aliases, respectively, to 'k', 'citiedcount' and 'citycount'.
    Friday, January 22, 2010 3:28 PM
  • Ya I saw this page before, it looks like the functionality I'm looking for will be added in crm5. I'm going to have to look into more options for this.

    Thank you for the suggestions.
    Friday, January 22, 2010 4:44 PM