count() returns a different amount than a simple select
SELECT count() FROM recordtype
This returns 41
SELECT count(id) FROM recordtype
This returns 41
SELECT id FROM recordtype
This returns 33 rows
I'm running these statements in SqlXplorer. Why the different results?
soql
asked 3 hours ago
DeanDean
1184
New contributor
Dean is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
SELECT count() FROM recordtype
This returns 41
SELECT count(id) FROM recordtype
This returns 41
SELECT id FROM recordtype
This returns 33 rows
I'm running these statements in SqlXplorer. Why the different results?
soql
asked 3 hours ago
DeanDean
1184
New contributor
Dean is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
FYIcount()andcount(id)are not at all the same thing in SOQL.
– Adrian Larson♦
3 hours ago
1
Good callout @AdrianLarson, edited accordingly.
– Dean
3 hours ago
I tried removing my Profile assignment to a specific RecordType to see if that would remove it from specific query results, but it did not for me. But my Profile does have ModifyAllData. Could you check to see if you see the same behavior? I don't have SqlXplorer, so I just ran these queries in Developer Console and Developer Workbench.
– Adrian Larson♦
2 hours ago
1
Could you try the same query in the Developer Console and/or Workbench, just to rule out any anomaly with SqlXplorer?
– Daniel Ballinger
2 hours ago
My user doesn't appear to have access to the workbench any longer (generally only uses the bulk api), one of our salesforce folks to try it out @DanielBallinger
– Dean
2 hours ago
add a comment |
SELECT count() FROM recordtype
This returns 41
SELECT count(id) FROM recordtype
This returns 41
SELECT id FROM recordtype
This returns 33 rows
I'm running these statements in SqlXplorer. Why the different results?
soql
asked 3 hours ago
DeanDean
1184
New contributor
Dean is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
SELECT count() FROM recordtype
This returns 41
SELECT count(id) FROM recordtype
This returns 41
SELECT id FROM recordtype
This returns 33 rows
I'm running these statements in SqlXplorer. Why the different results?
soql
soql
asked 3 hours ago
DeanDean
1184
New contributor
Dean is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 3 hours ago
DeanDean
1184
New contributor
Dean is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 3 hours ago
Dean
asked 3 hours ago
DeanDean
1184
New contributor
Dean is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 3 hours ago
DeanDean
1184
asked 3 hours ago
DeanDean
1184
1184
New contributor
Dean is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Dean is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Dean is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
FYIcount()andcount(id)are not at all the same thing in SOQL.
– Adrian Larson♦
3 hours ago
1
Good callout @AdrianLarson, edited accordingly.
– Dean
3 hours ago
I tried removing my Profile assignment to a specific RecordType to see if that would remove it from specific query results, but it did not for me. But my Profile does have ModifyAllData. Could you check to see if you see the same behavior? I don't have SqlXplorer, so I just ran these queries in Developer Console and Developer Workbench.
– Adrian Larson♦
2 hours ago
1
Could you try the same query in the Developer Console and/or Workbench, just to rule out any anomaly with SqlXplorer?
– Daniel Ballinger
2 hours ago
My user doesn't appear to have access to the workbench any longer (generally only uses the bulk api), one of our salesforce folks to try it out @DanielBallinger
– Dean
2 hours ago
add a comment |
1
FYIcount()andcount(id)are not at all the same thing in SOQL.
– Adrian Larson♦
3 hours ago
1
Good callout @AdrianLarson, edited accordingly.
– Dean
3 hours ago
I tried removing my Profile assignment to a specific RecordType to see if that would remove it from specific query results, but it did not for me. But my Profile does have ModifyAllData. Could you check to see if you see the same behavior? I don't have SqlXplorer, so I just ran these queries in Developer Console and Developer Workbench.
– Adrian Larson♦
2 hours ago
1
Could you try the same query in the Developer Console and/or Workbench, just to rule out any anomaly with SqlXplorer?
– Daniel Ballinger
2 hours ago
My user doesn't appear to have access to the workbench any longer (generally only uses the bulk api), one of our salesforce folks to try it out @DanielBallinger
– Dean
2 hours ago
1
1
FYI
count() and count(id) are not at all the same thing in SOQL.– Adrian Larson♦
3 hours ago
FYI
count() and count(id) are not at all the same thing in SOQL.– Adrian Larson♦
3 hours ago
1
1
Good callout @AdrianLarson, edited accordingly.
– Dean
3 hours ago
Good callout @AdrianLarson, edited accordingly.
– Dean
3 hours ago
I tried removing my Profile assignment to a specific RecordType to see if that would remove it from specific query results, but it did not for me. But my Profile does have ModifyAllData. Could you check to see if you see the same behavior? I don't have SqlXplorer, so I just ran these queries in Developer Console and Developer Workbench.
– Adrian Larson♦
2 hours ago
I tried removing my Profile assignment to a specific RecordType to see if that would remove it from specific query results, but it did not for me. But my Profile does have ModifyAllData. Could you check to see if you see the same behavior? I don't have SqlXplorer, so I just ran these queries in Developer Console and Developer Workbench.
– Adrian Larson♦
2 hours ago
1
1
Could you try the same query in the Developer Console and/or Workbench, just to rule out any anomaly with SqlXplorer?
– Daniel Ballinger
2 hours ago
Could you try the same query in the Developer Console and/or Workbench, just to rule out any anomaly with SqlXplorer?
– Daniel Ballinger
2 hours ago
My user doesn't appear to have access to the workbench any longer (generally only uses the bulk api), one of our salesforce folks to try it out @DanielBallinger
– Dean
2 hours ago
My user doesn't appear to have access to the workbench any longer (generally only uses the bulk api), one of our salesforce folks to try it out @DanielBallinger
– Dean
2 hours ago
add a comment |
1 Answer
1
active
oldest
votes
I think I know what is causing it. Or at the very least how to reproduce it.
I initially had 14 RecordType records defined in my dev org. Both the SELECT count() FROM recordtype and SELECT Id FROM recordtype agreed on this number.
Then I created a new Inactive record type on Account. The SELECT Id FROM recordtype went to 15 as expected.
However, SELECT count() FROM recordtype and SELECT count(Id) FROM recordtype both started to return an aggregate expression of 17.
What the extra two records are getting picked up by the count() and how they are related to the inactive record is a bit of a mystery.
Making the new record type active didn't reset the counts to the expected value...
Then I tried
SELECT Id, DeveloperName FROM recordtype Group by Id, DeveloperName
Which returned 17 records with distinct DeveloperName's...
The two additional RecordTypes were:
- 01270000000UR81AAG Progress
- 01270000000UR82AAG Completion
I can't query for them directly by Id. A bit more investigating and I found that they have the sObjectType of Metric (KeyPrefix 0WJ).
Progress has the Description:
Measure achievement based on how much is finished compared to a targeted value. Ideal for tracking hard numbers and percentages. For example: Convert 50 Leads or Achieve 100% Customer Satisfaction.
Completion has the Description:
Measure achievement based on whether or not the metric is finished. Ideal for tracking milestones, or when you don't have metrics based on hard numbers. For example: Become Salesforce Certified.
I suspect you are seeing an anomaly/bug where the count() aggregate function is exposing RecordType records that you otherwise don't have access to. E.g. You can't create or otherwise access Metric records.
answered 2 hours ago
Daniel BallingerDaniel Ballinger
72.1k15146387
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "459"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Dean is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f245728%2fcount-returns-a-different-amount-than-a-simple-select%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think I know what is causing it. Or at the very least how to reproduce it.
I initially had 14 RecordType records defined in my dev org. Both the SELECT count() FROM recordtype and SELECT Id FROM recordtype agreed on this number.
Then I created a new Inactive record type on Account. The SELECT Id FROM recordtype went to 15 as expected.
However, SELECT count() FROM recordtype and SELECT count(Id) FROM recordtype both started to return an aggregate expression of 17.
What the extra two records are getting picked up by the count() and how they are related to the inactive record is a bit of a mystery.
Making the new record type active didn't reset the counts to the expected value...
Then I tried
SELECT Id, DeveloperName FROM recordtype Group by Id, DeveloperName
Which returned 17 records with distinct DeveloperName's...
The two additional RecordTypes were:
- 01270000000UR81AAG Progress
- 01270000000UR82AAG Completion
I can't query for them directly by Id. A bit more investigating and I found that they have the sObjectType of Metric (KeyPrefix 0WJ).
Progress has the Description:
Measure achievement based on how much is finished compared to a targeted value. Ideal for tracking hard numbers and percentages. For example: Convert 50 Leads or Achieve 100% Customer Satisfaction.
Completion has the Description:
Measure achievement based on whether or not the metric is finished. Ideal for tracking milestones, or when you don't have metrics based on hard numbers. For example: Become Salesforce Certified.
I suspect you are seeing an anomaly/bug where the count() aggregate function is exposing RecordType records that you otherwise don't have access to. E.g. You can't create or otherwise access Metric records.
answered 2 hours ago
Daniel BallingerDaniel Ballinger
72.1k15146387
add a comment |
I think I know what is causing it. Or at the very least how to reproduce it.
I initially had 14 RecordType records defined in my dev org. Both the SELECT count() FROM recordtype and SELECT Id FROM recordtype agreed on this number.
Then I created a new Inactive record type on Account. The SELECT Id FROM recordtype went to 15 as expected.
However, SELECT count() FROM recordtype and SELECT count(Id) FROM recordtype both started to return an aggregate expression of 17.
What the extra two records are getting picked up by the count() and how they are related to the inactive record is a bit of a mystery.
Making the new record type active didn't reset the counts to the expected value...
Then I tried
SELECT Id, DeveloperName FROM recordtype Group by Id, DeveloperName
Which returned 17 records with distinct DeveloperName's...
The two additional RecordTypes were:
- 01270000000UR81AAG Progress
- 01270000000UR82AAG Completion
I can't query for them directly by Id. A bit more investigating and I found that they have the sObjectType of Metric (KeyPrefix 0WJ).
Progress has the Description:
Measure achievement based on how much is finished compared to a targeted value. Ideal for tracking hard numbers and percentages. For example: Convert 50 Leads or Achieve 100% Customer Satisfaction.
Completion has the Description:
Measure achievement based on whether or not the metric is finished. Ideal for tracking milestones, or when you don't have metrics based on hard numbers. For example: Become Salesforce Certified.
I suspect you are seeing an anomaly/bug where the count() aggregate function is exposing RecordType records that you otherwise don't have access to. E.g. You can't create or otherwise access Metric records.
answered 2 hours ago
Daniel BallingerDaniel Ballinger
72.1k15146387
add a comment |
I think I know what is causing it. Or at the very least how to reproduce it.
I initially had 14 RecordType records defined in my dev org. Both the SELECT count() FROM recordtype and SELECT Id FROM recordtype agreed on this number.
Then I created a new Inactive record type on Account. The SELECT Id FROM recordtype went to 15 as expected.
However, SELECT count() FROM recordtype and SELECT count(Id) FROM recordtype both started to return an aggregate expression of 17.
What the extra two records are getting picked up by the count() and how they are related to the inactive record is a bit of a mystery.
Making the new record type active didn't reset the counts to the expected value...
Then I tried
SELECT Id, DeveloperName FROM recordtype Group by Id, DeveloperName
Which returned 17 records with distinct DeveloperName's...
The two additional RecordTypes were:
- 01270000000UR81AAG Progress
- 01270000000UR82AAG Completion
I can't query for them directly by Id. A bit more investigating and I found that they have the sObjectType of Metric (KeyPrefix 0WJ).
Progress has the Description:
Measure achievement based on how much is finished compared to a targeted value. Ideal for tracking hard numbers and percentages. For example: Convert 50 Leads or Achieve 100% Customer Satisfaction.
Completion has the Description:
Measure achievement based on whether or not the metric is finished. Ideal for tracking milestones, or when you don't have metrics based on hard numbers. For example: Become Salesforce Certified.
I suspect you are seeing an anomaly/bug where the count() aggregate function is exposing RecordType records that you otherwise don't have access to. E.g. You can't create or otherwise access Metric records.
answered 2 hours ago
Daniel BallingerDaniel Ballinger
72.1k15146387
I think I know what is causing it. Or at the very least how to reproduce it.
I initially had 14 RecordType records defined in my dev org. Both the SELECT count() FROM recordtype and SELECT Id FROM recordtype agreed on this number.
Then I created a new Inactive record type on Account. The SELECT Id FROM recordtype went to 15 as expected.
However, SELECT count() FROM recordtype and SELECT count(Id) FROM recordtype both started to return an aggregate expression of 17.
What the extra two records are getting picked up by the count() and how they are related to the inactive record is a bit of a mystery.
Making the new record type active didn't reset the counts to the expected value...
Then I tried
SELECT Id, DeveloperName FROM recordtype Group by Id, DeveloperName
Which returned 17 records with distinct DeveloperName's...
The two additional RecordTypes were:
- 01270000000UR81AAG Progress
- 01270000000UR82AAG Completion
I can't query for them directly by Id. A bit more investigating and I found that they have the sObjectType of Metric (KeyPrefix 0WJ).
Progress has the Description:
Measure achievement based on how much is finished compared to a targeted value. Ideal for tracking hard numbers and percentages. For example: Convert 50 Leads or Achieve 100% Customer Satisfaction.
Completion has the Description:
Measure achievement based on whether or not the metric is finished. Ideal for tracking milestones, or when you don't have metrics based on hard numbers. For example: Become Salesforce Certified.
I suspect you are seeing an anomaly/bug where the count() aggregate function is exposing RecordType records that you otherwise don't have access to. E.g. You can't create or otherwise access Metric records.
answered 2 hours ago
Daniel BallingerDaniel Ballinger
72.1k15146387
edited 2 hours ago
answered 2 hours ago
Daniel BallingerDaniel Ballinger
72.1k15146387
answered 2 hours ago
Daniel BallingerDaniel Ballinger
72.1k15146387
answered 2 hours ago
Daniel BallingerDaniel Ballinger
72.1k15146387
72.1k15146387
add a comment |
add a comment |
Dean is a new contributor. Be nice, and check out our Code of Conduct.
Dean is a new contributor. Be nice, and check out our Code of Conduct.
Dean is a new contributor. Be nice, and check out our Code of Conduct.
Dean is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Salesforce Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f245728%2fcount-returns-a-different-amount-than-a-simple-select%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
FYI
count()andcount(id)are not at all the same thing in SOQL.– Adrian Larson♦
3 hours ago
1
Good callout @AdrianLarson, edited accordingly.
– Dean
3 hours ago
I tried removing my Profile assignment to a specific RecordType to see if that would remove it from specific query results, but it did not for me. But my Profile does have ModifyAllData. Could you check to see if you see the same behavior? I don't have SqlXplorer, so I just ran these queries in Developer Console and Developer Workbench.
– Adrian Larson♦
2 hours ago
1
Could you try the same query in the Developer Console and/or Workbench, just to rule out any anomaly with SqlXplorer?
– Daniel Ballinger
2 hours ago
My user doesn't appear to have access to the workbench any longer (generally only uses the bulk api), one of our salesforce folks to try it out @DanielBallinger
– Dean
2 hours ago