August 14, 2018 at 11:08 am
Hello Paul,
Whenever COUNT(*) is used in the SELECT clause of a statement, it has the 'same value' if it is repeated. It is the count of number of occurences of the 'same' fields (same as in same content) in the group by clause.
See the example :SELECT [service], [vehicle],
COUNT(*) AS Quantity,
COUNT(*) AS Another_Count,
COUNT(*)*2 as Double_Count,
COUNT(*)/2 as HALF_Count,
1.0*COUNT(*)/2 as HALF_Count_but_with_fractions,
SUM(subtotal) AS subtotal,
SUM(TaxAmount) AS TaxAmount,
SUM(total) AS Total,
SUM(total)*1.0 / COUNT(*) AS Price_Per_Unit
FROM jobs
GROUP BY [service], [vehicle]
;
All the 'count(*)' in the above query is the number of occurences for the same value of service and vehicle.
It does not matter what I do with the count, so in one instance it is called the Quantity and in one instance it is called Another_Count. They both have the same value. Double_count is the same count but multiplied by 2.
The multiplying by 1.0 is to force the outcome to 'include' fractions as wel and not to give the anwser as an integer.
The CTE example is given, because I like to chop up the query in manageble parts, in the first part (A), de Quantity is determined by the count(*) in the second part the Quantity is used. This construction avoids using the same 'code', namely count(*) more than once. For count(*) this is not an issue, but for larger expressions I like to have the expression once, because than if the expression contains a mistake, it at least will be consistent. Using a 'formule' twice gives the risk that there is a slight difficult to notice difference. And checking one does not guarantee that the second version is also correct. So I like the CTE construct to avoid using constructions more than once and for the 'clearity' of a CTE construction can deliver.
Ben
August 15, 2018 at 2:13 am
Ok Ben. Thank you very much for your time and explanation.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply