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?

edited Jan 7 at 23:34

asked Jan 7 at 23:29

Dean

1334

1

FYI count() and count(id) are not at all the same thing in SOQL.

– Adrian Larson♦
Jan 7 at 23:32

1

Good callout @AdrianLarson, edited accordingly.

– Dean
Jan 7 at 23:35

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♦
Jan 7 at 23:42

1

Could you try the same query in the Developer Console and/or Workbench, just to rule out any anomaly with SqlXplorer?

– Daniel Ballinger
Jan 7 at 23:42

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
Jan 8 at 0:07

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?

edited Jan 7 at 23:34

asked Jan 7 at 23:29

Dean

1334

1

FYI count() and count(id) are not at all the same thing in SOQL.

– Adrian Larson♦
Jan 7 at 23:32

1

Good callout @AdrianLarson, edited accordingly.

– Dean
Jan 7 at 23:35

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♦
Jan 7 at 23:42

1

Could you try the same query in the Developer Console and/or Workbench, just to rule out any anomaly with SqlXplorer?

– Daniel Ballinger
Jan 7 at 23:42

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
Jan 8 at 0:07

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?

edited Jan 7 at 23:34

asked Jan 7 at 23:29

Dean

1334

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

edited Jan 7 at 23:34

asked Jan 7 at 23:29

Dean

1334

edited Jan 7 at 23:34

asked Jan 7 at 23:29

Dean

1334

edited Jan 7 at 23:34

asked Jan 7 at 23:29

Dean

1334

asked Jan 7 at 23:29

Dean

1334

asked Jan 7 at 23:29

Dean

1334

1

FYI count() and count(id) are not at all the same thing in SOQL.

– Adrian Larson♦
Jan 7 at 23:32

1

Good callout @AdrianLarson, edited accordingly.

– Dean
Jan 7 at 23:35

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♦
Jan 7 at 23:42

1

Could you try the same query in the Developer Console and/or Workbench, just to rule out any anomaly with SqlXplorer?

– Daniel Ballinger
Jan 7 at 23:42

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
Jan 8 at 0:07

add a comment |

1

FYI count() and count(id) are not at all the same thing in SOQL.

– Adrian Larson♦
Jan 7 at 23:32

1

Good callout @AdrianLarson, edited accordingly.

– Dean
Jan 7 at 23:35

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♦
Jan 7 at 23:42

1

Could you try the same query in the Developer Console and/or Workbench, just to rule out any anomaly with SqlXplorer?

– Daniel Ballinger
Jan 7 at 23:42

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
Jan 8 at 0:07

FYI count() and count(id) are not at all the same thing in SOQL.

– Adrian Larson♦
Jan 7 at 23:32

Good callout @AdrianLarson, edited accordingly.

– Dean
Jan 7 at 23:35

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♦
Jan 7 at 23:42

Could you try the same query in the Developer Console and/or Workbench, just to rule out any anomaly with SqlXplorer?

– Daniel Ballinger
Jan 7 at 23:42

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
Jan 8 at 0:07

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.

edited Jan 8 at 0:18

answered Jan 8 at 0:00

Daniel Ballinger

72.7k15149392

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

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.

edited Jan 8 at 0:18

answered Jan 8 at 0:00

Daniel Ballinger

72.7k15149392

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.

edited Jan 8 at 0:18

answered Jan 8 at 0:00

Daniel Ballinger

72.7k15149392

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.

edited Jan 8 at 0:18

answered Jan 8 at 0:00

Daniel Ballinger

72.7k15149392

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.

edited Jan 8 at 0:18

answered Jan 8 at 0:00

Daniel Ballinger

72.7k15149392

edited Jan 8 at 0:18

answered Jan 8 at 0:00

Daniel Ballinger

72.7k15149392

answered Jan 8 at 0:00

Daniel Ballinger

72.7k15149392

answered Jan 8 at 0:00

Daniel Ballinger

72.7k15149392

add a comment |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu