Chris February 2016

Can you use the result of a count in another formula

SELECT DISTINCT Course_NO, count(section_ID) Sections
FROM SECTION
group by course_No
having count(section_id) > 3;

I need to use the values of count(section_id) to determine the total capacity by mulitiplying each value x the capacity for each section.

Answers


Tim Southard February 2016

Have you considered using a "with" statement?

;with SampleData as 
(
SELECT DISTINCT 
    Course_NO, 
    COUNT(section_ID) Sections 
    FROM SECTION 
    GROUP BY course_No 
    HAVING COUNT(section_id) > 3
)
SELECT * FROM SampleData

This will allow you to easily re-use those counts.


singhsac February 2016

You don't have to use DISTINCT when grouping, your query will work fine with it.

SELECT
    Course_NO
    , COUNT(section_ID) Sections
FROM
    SECTION
GROUP BY
    course_No
HAVING
    COUNT(section_id) > 3;

Now, to use the COUNT() value, from what I've understood - you want the product of course_NO times the occurrence of section_ID for all the section_ID occurring more than 3 times. You already have the COUNT from your own query, simply display another column with the product:

SELECT
    Course_NO
    , COUNT(section_ID) [Sections]
    , Course_NO * COUNT(section_ID) [Capacity]
FROM
    SECTION
GROUP BY
    course_No
HAVING
    COUNT(section_id) > 3;

Or if you have a fixed number that you want to multiply with the count, replace course_NO in the capacity column with that value (the second query above)

Post Status

Asked in February 2016
Viewed 3,323 times
Voted 14
Answered 2 times

Search




Leave an answer