September 20, 2018 at 12:38 am
Hi
I have some difficulties getting a COUNT(*) on my below SQL Query, atleast one that returns the correct count .. the below script is returning 35 unique rows, but when I replace the SELECT ITEMS with COUNT(*), and remove the ORDER BY clause (otherwise it fails) it returns 134 instead of the 35 that the full script returns .. can someone help figuring out how to do this?
SELECT DISTINCT [RPA_Softomotive].[dbo].[folders].name AS Afdeling, [RPA_Softomotive].[dbo].[folders].parent_id, [RPA_Softomotive].[dbo].[folders].path, [RPA_Softomotive].[dbo].[job_info].folder_id, [RPA_Softomotive].[dbo].[job_info].name AS 'Process Navn', job_info.id, job_code.commit_version_id, job_info_tags.commit_version_id AS Expr2, job_code.commit_timestamp AS 'Dato for version',
job_code.commit_version_alias AS Version, job_code.commit_message AS Kommentar, folders.id AS Expr3
FROM folders INNER JOIN
job_info_tags INNER JOIN
job_info ON job_info_tags.job_id = job_info.id INNER JOIN
job_code ON job_info_tags.commit_version_id = job_code.commit_version_id ON folders.id = job_info.folder_id
WHERE [RPA_Softomotive].[dbo].[job_info_tags].tag = 1
and ([RPA_Softomotive].[dbo].[folders].id <> 2 and [RPA_Softomotive].[dbo].[folders].id <> 3
and [RPA_Softomotive].[dbo].[folders].id <> 4 and [RPA_Softomotive].[dbo].[folders].id <> 5 and [RPA_Softomotive].[dbo].[folders].id <> 6 )
order by [RPA_Softomotive].[dbo].[folders].parent_id, [RPA_Softomotive].[dbo].[job_info].folder_id,[RPA_Softomotive].[dbo].[folders].name
Best Regards
Stig 🙂
September 20, 2018 at 2:57 am
sk 939 - Thursday, September 20, 2018 12:38 AMHiI have some difficulties getting a COUNT(*) on my below SQL Query, atleast one that returns the correct count .. the below script is returning 35 unique rows, but when I replace the SELECT ITEMS with COUNT(*), and remove the ORDER BY clause (otherwise it fails) it returns 134 instead of the 35 that the full script returns .. can someone help figuring out how to do this?
SELECT DISTINCT [RPA_Softomotive].[dbo].[folders].name AS Afdeling, [RPA_Softomotive].[dbo].[folders].parent_id, [RPA_Softomotive].[dbo].[folders].path, [RPA_Softomotive].[dbo].[job_info].folder_id, [RPA_Softomotive].[dbo].[job_info].name AS 'Process Navn', job_info.id, job_code.commit_version_id, job_info_tags.commit_version_id AS Expr2, job_code.commit_timestamp AS 'Dato for version',
job_code.commit_version_alias AS Version, job_code.commit_message AS Kommentar, folders.id AS Expr3
FROM folders INNER JOIN
job_info_tags INNER JOIN
job_info ON job_info_tags.job_id = job_info.id INNER JOIN
job_code ON job_info_tags.commit_version_id = job_code.commit_version_id ON folders.id = job_info.folder_id
WHERE [RPA_Softomotive].[dbo].[job_info_tags].tag = 1
and ([RPA_Softomotive].[dbo].[folders].id <> 2 and [RPA_Softomotive].[dbo].[folders].id <> 3
and [RPA_Softomotive].[dbo].[folders].id <> 4 and [RPA_Softomotive].[dbo].[folders].id <> 5 and [RPA_Softomotive].[dbo].[folders].id <> 6 )
order by [RPA_Softomotive].[dbo].[folders].parent_id, [RPA_Softomotive].[dbo].[job_info].folder_id,[RPA_Softomotive].[dbo].[folders].nameBest Regards
Stig 🙂
When you use DISTINCT on a query which would otherwise return duplicate rows, the number of rows returned will depend on the columns you include in the SELECT list. Eliminate all of the columns, which is what you are doing with COUNT(*), and you get the number of rows including duplicates. There are numerous ways of dealing with this. The easiest is to set the initial query up as a CTE and do a SELECT COUNT(*) from it. You could add a column to the existing data output which would give a count of all rows (or number the rows from 1 to total rows). The option I'd prefer to use is to identify where the dupes are arising from. Often this is one single table in the FROM list and treating it as a derived table, aggregated to the same cardinality as the other tables, is a good way to go. Whichever method you choose, it's always good to get the starting point as clean as possible:
SELECT DISTINCT
f.name AS Afdeling,
f.parent_id,
f.path,
ji.folder_id,
ji.name AS 'Process Navn',
ji.id,
jc.commit_version_id,
jt.commit_version_id AS Expr2,
jc.commit_timestamp AS 'Dato for version',
jc.commit_version_alias AS Version,
jc.commit_message AS Kommentar,
f.id AS Expr3
FROM folders f
INNER JOIN job_info ji
ON f.id = ji.folder_id
INNER JOIN job_info_tags jt
ON jt.job_id = ji.id
INNER JOIN job_code jc
ON jt.commit_version_id = jc.commit_version_id
WHERE jt.tag = 1
AND f.id NOT IN (2,3,4,5,6)
ORDER BY f.parent_id, ji.folder_id, f.name
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 20, 2018 at 4:29 am
Hi ChrisM,
Thank you very much for your explanation, and the cleaning of the code - that is REALLY a lot more simple than my piece, GREAT!! .. Your post got me some of the way, by adding "select @@ROWCOUNT as "NOOFRECORDS" to the query it returns the correct count in SQL Management Studio, but I need to use it in ASP Classic, and there I get an error when trying to display it using <%=objprod("NOOFRECORDS")%> .. doing a little google search it seems that the easy way in this scenario is not the correct approach for me .. I will look at your other suggestions, and if you have some extra input in the meantime I would be very happy 🙂
Best Regards
Stig 🙂
September 20, 2018 at 3:13 pm
SELECT Count(*)-- DISTINCT
-- [RPA_Softomotive].[dbo].[folders].name AS Afdeling
--, [RPA_Softomotive].[dbo].[folders].parent_id
--, [RPA_Softomotive].[dbo].[folders].path
--, [RPA_Softomotive].[dbo].[job_info].folder_id
--, [RPA_Softomotive].[dbo].[job_info].name AS 'Process Navn'
--, job_info.id
--, job_code.commit_version_id
--, job_info_tags.commit_version_id AS Expr2
--, job_code.commit_timestamp AS 'Dato for version'
--, job_code.commit_version_alias AS Version
--, job_code.commit_message AS Kommentar
--, folders.id AS Expr3
FROM
folders
INNER JOIN
job_info_tags
INNER JOIN
job_info ON job_info_tags.job_id = job_info.id
INNER JOIN
job_code ON job_info_tags.commit_version_id = job_code.commit_version_id ON folders.id = job_info.folder_id
WHERE
[RPA_Softomotive].[dbo].[job_info_tags].tag = 1
AND
( [RPA_Softomotive].[dbo].[folders].id <> 2
AND [RPA_Softomotive].[dbo].[folders].id <> 3
AND [RPA_Softomotive].[dbo].[folders].id <> 4
AND [RPA_Softomotive].[dbo].[folders].id <> 5
AND [RPA_Softomotive].[dbo].[folders].id <> 6 )
GROUP BY
[RPA_Softomotive].[dbo].[folders].name -- AS Afdeling
, [RPA_Softomotive].[dbo].[folders].parent_id
, [RPA_Softomotive].[dbo].[folders].path
, [RPA_Softomotive].[dbo].[job_info].folder_id
, [RPA_Softomotive].[dbo].[job_info].name --AS 'Process Navn'
, job_info.id
, job_code.commit_version_id
, job_info_tags.commit_version_id -- AS Expr2
, job_code.commit_timestamp -- AS 'Dato for version'
, job_code.commit_version_alias -- AS Version
, job_code.commit_message -- AS Kommentar
, folders.id -- AS Expr3
--ORDER BYSeptember 20, 2018 at 3:44 pm
sk 939 - Thursday, September 20, 2018 12:38 AMHiI have some difficulties getting a COUNT(*) on my below SQL Query, atleast one that returns the correct count .. the below script is returning 35 unique rows, but when I replace the SELECT ITEMS with COUNT(*), and remove the ORDER BY clause (otherwise it fails) it returns 134 instead of the 35 that the full script returns .. can someone help figuring out how to do this?
SELECT DISTINCT [RPA_Softomotive].[dbo].[folders].name AS Afdeling, [RPA_Softomotive].[dbo].[folders].parent_id, [RPA_Softomotive].[dbo].[folders].path, [RPA_Softomotive].[dbo].[job_info].folder_id, [RPA_Softomotive].[dbo].[job_info].name AS 'Process Navn', job_info.id, job_code.commit_version_id, job_info_tags.commit_version_id AS Expr2, job_code.commit_timestamp AS 'Dato for version',
job_code.commit_version_alias AS Version, job_code.commit_message AS Kommentar, folders.id AS Expr3
FROM folders INNER JOIN
job_info_tags INNER JOIN
job_info ON job_info_tags.job_id = job_info.id INNER JOIN
job_code ON job_info_tags.commit_version_id = job_code.commit_version_id ON folders.id = job_info.folder_id
WHERE [RPA_Softomotive].[dbo].[job_info_tags].tag = 1
and ([RPA_Softomotive].[dbo].[folders].id <> 2 and [RPA_Softomotive].[dbo].[folders].id <> 3
and [RPA_Softomotive].[dbo].[folders].id <> 4 and [RPA_Softomotive].[dbo].[folders].id <> 5 and [RPA_Softomotive].[dbo].[folders].id <> 6 )
order by [RPA_Softomotive].[dbo].[folders].parent_id, [RPA_Softomotive].[dbo].[job_info].folder_id,[RPA_Softomotive].[dbo].[folders].nameBest Regards
Stig 🙂
Remove the ORDER BY and write it like this:SELECT Count(*) count
FROM (your query here with the distinct) a
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply