0

I want to be able to show a report with groups that have more than one of an asset. In SQL Server I would do it with a query like this:

Select AccountID, Count(AssetID) From Assets Where AssetName Like 'My asset' And Count(AssetID) > 1 Group By AccountID

Which would only return the Accounts that have more than 1 of that specific asset.

I can do all of that in report builder in an an "Accounts with Assets" report, except for the Count(AssetID) > 1 part.
Is that possible?

I would love to avoid having to scroll through several thousand rows looking for the few that got doubled up in the data import.

1

1 Answer 1

2

You can't do this with a report, but you can certainly do this with a query. Click on the Gear ⚙ icon, and then Developer Console, click on the Query Editor tab, and enter the query as follows:

select count(id) assetCount, accountid Id
from asset
group by accountid
having count(id) > 1

This will give you just those accounts with more than one asset.

3
  • This kinda works. Not as convenient as doing it in a report, since I have to copy each account id to the address bar to bring up the record that needs to be fixed, but it kinda works. This is a seperate question, but does dev console allow for using IN? like "Where AccountID in (select AccountID from asset ...)
    – AndyD273
    Commented Feb 2, 2021 at 15:50
  • @AndyD273 You can use any query tool you want, such as Informatica, Apex Data Loader, etc. For mass updating/deleting/whatever records, an ETL (Extract, Transform, Load) tool is preferable.
    – sfdcfox
    Commented Feb 2, 2021 at 16:12
  • @sfdcfox So does that mean if we merge 2 reports, we cannot get a group-by count for a column.
    – Salih
    Commented Feb 22, 2023 at 11:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .