Mixing
SQL statement unique ID and then grouping by unique ID and counting a distinct other ID group
1
I want to make a query that makes the unique ID concatenate and then group by this unique ID and then count another field by distinct. I get the output but the count is off. I verified through another source.
SELECT
CONCAT(x, y, z, a) AS 'uniqueid',
COUNT(DISTINCT id) AS 'count'
FROM bv
GROUP BY
x, y, z, a
sql
edited Nov 21 '18 at 5:26
marc_s
575k12811101257
asked Nov 20 '18 at 23:14
funSizesfunSizes
277
add a comment |
1
I want to make a query that makes the unique ID concatenate and then group by this unique ID and then count another field by distinct. I get the output but the count is off. I verified through another source.
SELECT
CONCAT(x, y, z, a) AS 'uniqueid',
COUNT(DISTINCT id) AS 'count'
FROM bv
GROUP BY
x, y, z, a
sql
edited Nov 21 '18 at 5:26
marc_s
575k12811101257
asked Nov 20 '18 at 23:14
funSizesfunSizes
277
What is the sql that was used to verify through another source? Please show the other source's sql. What is the timing of these two counts?? same instant?? or some time lag? Are all counts higher/mixed/lower? Please share some data, and above count and another source count...!!
– donPablo
Nov 20 '18 at 23:22
The other source being used is tableau unfortunately I can't share the data. Tableau makes the calculated field similar to the concatenate and then counts the id file and gives me 8k more records compared to the SQL query. The id number can be used several times that's why we want to count by Id. At the end we want to filter it by distinct id = 1
– funSizes
Nov 20 '18 at 23:25
I simply don't understand what count is off. Can you give an example of what you mean?
– Gordon Linoff
Nov 20 '18 at 23:44
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your query is invalid standard SQL). Please add a tag for the database product you are using
– a_horse_with_no_name
Nov 21 '18 at 6:49
It seems that 'uniqueid' and 'id' are separate columns. Yet they (probably) have different values. And Tableau has a "SIMILAR Field" yet not exactly the same field? If different from concatenate, no wonder different results are being gotten? Also, does tableau count(id) or count(distinct id)?
– donPablo
Nov 22 '18 at 0:46
add a comment |
1
1
1
I want to make a query that makes the unique ID concatenate and then group by this unique ID and then count another field by distinct. I get the output but the count is off. I verified through another source.
SELECT
CONCAT(x, y, z, a) AS 'uniqueid',
COUNT(DISTINCT id) AS 'count'
FROM bv
GROUP BY
x, y, z, a
sql
edited Nov 21 '18 at 5:26
marc_s
575k12811101257
asked Nov 20 '18 at 23:14
funSizesfunSizes
277
I want to make a query that makes the unique ID concatenate and then group by this unique ID and then count another field by distinct. I get the output but the count is off. I verified through another source.
SELECT
CONCAT(x, y, z, a) AS 'uniqueid',
COUNT(DISTINCT id) AS 'count'
FROM bv
GROUP BY
x, y, z, a
sql
sql
edited Nov 21 '18 at 5:26
marc_s
575k12811101257
asked Nov 20 '18 at 23:14
funSizesfunSizes
277
edited Nov 21 '18 at 5:26
marc_s
575k12811101257
asked Nov 20 '18 at 23:14
funSizesfunSizes
277
edited Nov 21 '18 at 5:26
marc_s
575k12811101257
edited Nov 21 '18 at 5:26
marc_s
575k12811101257
edited Nov 21 '18 at 5:26
marc_s
575k12811101257
575k12811101257
asked Nov 20 '18 at 23:14
funSizesfunSizes
277
asked Nov 20 '18 at 23:14
funSizesfunSizes
277
asked Nov 20 '18 at 23:14
funSizesfunSizes
277
277
What is the sql that was used to verify through another source? Please show the other source's sql. What is the timing of these two counts?? same instant?? or some time lag? Are all counts higher/mixed/lower? Please share some data, and above count and another source count...!!
– donPablo
Nov 20 '18 at 23:22
The other source being used is tableau unfortunately I can't share the data. Tableau makes the calculated field similar to the concatenate and then counts the id file and gives me 8k more records compared to the SQL query. The id number can be used several times that's why we want to count by Id. At the end we want to filter it by distinct id = 1
– funSizes
Nov 20 '18 at 23:25
I simply don't understand what count is off. Can you give an example of what you mean?
– Gordon Linoff
Nov 20 '18 at 23:44
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your query is invalid standard SQL). Please add a tag for the database product you are using
– a_horse_with_no_name
Nov 21 '18 at 6:49
It seems that 'uniqueid' and 'id' are separate columns. Yet they (probably) have different values. And Tableau has a "SIMILAR Field" yet not exactly the same field? If different from concatenate, no wonder different results are being gotten? Also, does tableau count(id) or count(distinct id)?
– donPablo
Nov 22 '18 at 0:46
add a comment |
What is the sql that was used to verify through another source? Please show the other source's sql. What is the timing of these two counts?? same instant?? or some time lag? Are all counts higher/mixed/lower? Please share some data, and above count and another source count...!!
– donPablo
Nov 20 '18 at 23:22
The other source being used is tableau unfortunately I can't share the data. Tableau makes the calculated field similar to the concatenate and then counts the id file and gives me 8k more records compared to the SQL query. The id number can be used several times that's why we want to count by Id. At the end we want to filter it by distinct id = 1
– funSizes
Nov 20 '18 at 23:25
I simply don't understand what count is off. Can you give an example of what you mean?
– Gordon Linoff
Nov 20 '18 at 23:44
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your query is invalid standard SQL). Please add a tag for the database product you are using
– a_horse_with_no_name
Nov 21 '18 at 6:49
It seems that 'uniqueid' and 'id' are separate columns. Yet they (probably) have different values. And Tableau has a "SIMILAR Field" yet not exactly the same field? If different from concatenate, no wonder different results are being gotten? Also, does tableau count(id) or count(distinct id)?
– donPablo
Nov 22 '18 at 0:46
What is the sql that was used to verify through another source? Please show the other source's sql. What is the timing of these two counts?? same instant?? or some time lag? Are all counts higher/mixed/lower? Please share some data, and above count and another source count...!!
– donPablo
Nov 20 '18 at 23:22
What is the sql that was used to verify through another source? Please show the other source's sql. What is the timing of these two counts?? same instant?? or some time lag? Are all counts higher/mixed/lower? Please share some data, and above count and another source count...!!
– donPablo
Nov 20 '18 at 23:22
The other source being used is tableau unfortunately I can't share the data. Tableau makes the calculated field similar to the concatenate and then counts the id file and gives me 8k more records compared to the SQL query. The id number can be used several times that's why we want to count by Id. At the end we want to filter it by distinct id = 1
– funSizes
Nov 20 '18 at 23:25
The other source being used is tableau unfortunately I can't share the data. Tableau makes the calculated field similar to the concatenate and then counts the id file and gives me 8k more records compared to the SQL query. The id number can be used several times that's why we want to count by Id. At the end we want to filter it by distinct id = 1
– funSizes
Nov 20 '18 at 23:25
I simply don't understand what count is off. Can you give an example of what you mean?
– Gordon Linoff
Nov 20 '18 at 23:44
I simply don't understand what count is off. Can you give an example of what you mean?
– Gordon Linoff
Nov 20 '18 at 23:44
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your query is invalid standard SQL). Please add a tag for the database product you are using
– a_horse_with_no_name
Nov 21 '18 at 6:49
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your query is invalid standard SQL). Please add a tag for the database product you are using
– a_horse_with_no_name
Nov 21 '18 at 6:49
It seems that 'uniqueid' and 'id' are separate columns. Yet they (probably) have different values. And Tableau has a "SIMILAR Field" yet not exactly the same field? If different from concatenate, no wonder different results are being gotten? Also, does tableau count(id) or count(distinct id)?
– donPablo
Nov 22 '18 at 0:46
It seems that 'uniqueid' and 'id' are separate columns. Yet they (probably) have different values. And Tableau has a "SIMILAR Field" yet not exactly the same field? If different from concatenate, no wonder different results are being gotten? Also, does tableau count(id) or count(distinct id)?
– donPablo
Nov 22 '18 at 0:46
add a comment |
1 Answer
1
active
oldest
votes
0
You need to group on the same non-aggregated fields (or composite fields in this case) as you have in your SELECT list. You should be able to use the query below to get what you're looking for:
SELECT
CONCAT(x,y,z,a) AS 'uniqueid'
,COUNT(DISTINCT id) AS 'count'
FROM bv
GROUP BY CONCAT(x,y,z,a)
answered Nov 20 '18 at 23:33
digital.aarondigital.aaron
3,2131330
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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');
});
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%2fstackoverflow.com%2fquestions%2f53403035%2fsql-statement-unique-id-and-then-grouping-by-unique-id-and-counting-a-distinct-o%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
0
You need to group on the same non-aggregated fields (or composite fields in this case) as you have in your SELECT list. You should be able to use the query below to get what you're looking for:
SELECT
CONCAT(x,y,z,a) AS 'uniqueid'
,COUNT(DISTINCT id) AS 'count'
FROM bv
GROUP BY CONCAT(x,y,z,a)
answered Nov 20 '18 at 23:33
digital.aarondigital.aaron
3,2131330
add a comment |
0
You need to group on the same non-aggregated fields (or composite fields in this case) as you have in your SELECT list. You should be able to use the query below to get what you're looking for:
SELECT
CONCAT(x,y,z,a) AS 'uniqueid'
,COUNT(DISTINCT id) AS 'count'
FROM bv
GROUP BY CONCAT(x,y,z,a)
answered Nov 20 '18 at 23:33
digital.aarondigital.aaron
3,2131330
add a comment |
0
0
0
You need to group on the same non-aggregated fields (or composite fields in this case) as you have in your SELECT list. You should be able to use the query below to get what you're looking for:
SELECT
CONCAT(x,y,z,a) AS 'uniqueid'
,COUNT(DISTINCT id) AS 'count'
FROM bv
GROUP BY CONCAT(x,y,z,a)
answered Nov 20 '18 at 23:33
digital.aarondigital.aaron
3,2131330
You need to group on the same non-aggregated fields (or composite fields in this case) as you have in your SELECT list. You should be able to use the query below to get what you're looking for:
SELECT
CONCAT(x,y,z,a) AS 'uniqueid'
,COUNT(DISTINCT id) AS 'count'
FROM bv
GROUP BY CONCAT(x,y,z,a)
answered Nov 20 '18 at 23:33
digital.aarondigital.aaron
3,2131330
answered Nov 20 '18 at 23:33
digital.aarondigital.aaron
3,2131330
answered Nov 20 '18 at 23:33
digital.aarondigital.aaron
3,2131330
answered Nov 20 '18 at 23:33
digital.aarondigital.aaron
3,2131330
3,2131330
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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');
});
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%2fstackoverflow.com%2fquestions%2f53403035%2fsql-statement-unique-id-and-then-grouping-by-unique-id-and-counting-a-distinct-o%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
What is the sql that was used to verify through another source? Please show the other source's sql. What is the timing of these two counts?? same instant?? or some time lag? Are all counts higher/mixed/lower? Please share some data, and above count and another source count...!!
– donPablo
Nov 20 '18 at 23:22
The other source being used is tableau unfortunately I can't share the data. Tableau makes the calculated field similar to the concatenate and then counts the id file and gives me 8k more records compared to the SQL query. The id number can be used several times that's why we want to count by Id. At the end we want to filter it by distinct id = 1
– funSizes
Nov 20 '18 at 23:25
I simply don't understand what count is off. Can you give an example of what you mean?
– Gordon Linoff
Nov 20 '18 at 23:44
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and your query is invalid standard SQL). Please add a tag for the database product you are using
– a_horse_with_no_name
Nov 21 '18 at 6:49
It seems that 'uniqueid' and 'id' are separate columns. Yet they (probably) have different values. And Tableau has a "SIMILAR Field" yet not exactly the same field? If different from concatenate, no wonder different results are being gotten? Also, does tableau count(id) or count(distinct id)?
– donPablo
Nov 22 '18 at 0:46