Alias in where query in SOQL

Question

I am trying to take the count from the object Lead using SOQL. When I am hitting the below mention query, I am getting the results.

select 'Lead' as source_table,count(*) as source_count from Lead

But when I am trying to give where condition with this query, then it is throwing me the error.

select 'Lead' as source_table,count() as source_count from Lead where CreatedDate > 2020-02-24T09:43:51Z

Is there anything that I am missing.

eyescream eyescream · Accepted Answer · 2020-03-03T07:06:45

Your query is not a valid SOQL, where exactly are you doing it? In real Salesforce (Apex, developer console, Salesforce APIs) or do you work on some copy of data imported to MSSQL for example?

Even your basic form (select 'Lead' as source_table,count(*) as source_count from Lead) will not parse OK in SOQL. There's no AS keyword and no dummy columns.

Closest would be

SELECT COUNT(Id) source_count
FROM Lead
WHERE CreatedDate > 2020-02-24T09:43:51Z

There will be no artificial column with "Lead" in it. If you need some key-value thing across multiple database tables you'd have to process results in Apex, maybe make a helper class to store results, maybe a Map<String, Integer> would be enough. But if you're doing it as API access then much better idea would be to access the dedicated APIs

getUpdated REST API - has "updated" and "deleted" versions, if you really need "created" it's not that great unless you filter them out later somehow
record count across multiple objects - but it's really count in table, similar to Setup -> Storage Usage. No way to pass a WHERE clause
ask your SF admin to build a report that does what you need (you don't need details, just a count, right?), then access the results with Analytics API. Or even build the report on the fly: example

Alias in where query in SOQL

1 Answers