Clever Geek Handbook

You need to account for different values ​​using a composite primary key

I need to create a report that displays the name of different locations, as well as the number of unique course offerings in those locations. The report should only indicate the city and number. unique course offers and sorted by the largest number of unique offers. The problem I am having is this offer table contains a composite primary key (suggesting start date and course code)

I run this SQL query and an error occurs as you cannot count multiple columns. But I'm not sure how to split the bill.

SELECT 
    offlocation AS city, COUNT(distinct offbegindate,crscode) as no. of unique course offerings
FROM
    offering
GROUP BY offlocation
ORDER BY COUNT(distinct offbegindate,crscode) as no. of unique course offerings DESC;

Any help would be greatly appreciated.

+3


source to share


3 answers


Just small adjustments, "Row Types" for different values:



SELECT 
    offlocation AS city,
    COUNT(distinct (offbegindate,crscode)) as no_of_unique_course_offerings
FROM
    offering
GROUP BY offlocation
ORDER BY no_of_unique_course_offerings DESC;
+3


source


You can try to concatenate the values:



SELECT 
    offlocation AS city, COUNT(distinct offbegindate || '-' || crscode) as no. of unique course offerings
FROM
    offering
GROUP BY offlocation
ORDER BY COUNT(distinct offbegindate,crscode) as no. of unique course offerings DESC;
+1


source


Hope this works:

select offlocation AS city , count(*)  as cnt from
(

  select distinct offlocation,offbegindate,crscode from offering

) v 

group by city 
order by cnt desc;
+1


source






More articles:


All Articles