CODEWITHSUNDEEP
sql-servercount
Adam
Adam

Reputation: 6122

Distinct values with occurrences count

I want to count the number of times a vendor title occurs in a resultset, but if I use COUNT combined with GROUP BY, I only get either a 0 or 1 in the resultset

e.g. my results now look like this:

id   vendortitle      cnt
184  Hotel            1
198  A3               1
199  Las Vegas        1
200  Hotel-Restaurant 1
1252 Hansen           1
1253 Sunrise          1
1255 NULL             0
1256 Winsel           1
1257 Olde North       1
1258 A Castle         1
1259 A Castle         1
1262 Restaurant Rich  1
1263 NULL             0
1264 NULL             0
1265 NULL             0
1266 NULL             0
1269 My venue         1
1270 My venue         1
1271 My venue         1
1272 My venue         1

But I want this (I don't really actually need the NULL values):

id   vendortitle      cnt
184  Hotel            1
198  A3               1
199  Las Vegas        1
200  Hotel-Restaurant 1
1252 Hansen           1
1253 Sunrise          1
1255 NULL             5
1256 Winsel           1
1257 Olde North       1
1258 A Castle         2
1262 Restaurant Rich  1
1269 My venue         4

My SQL statement:

SELECT DISTINCT(vendortitle),id,COUNT(vendortitle) as cnt FROM (select ROW_NUMBER() OVER (ORDER BY vendortitle DESC) as RowNum,
id,vendortitle
FROM 
(
    SELECT 
      uf.id,coalesce(l.title, a.title) as vendortitle
    FROM userfavorites uf
    INNER JOIN vendor_photos vp ON uf.objectid = vp.id 

            LEFT JOIN homes l on vp.objecttype= 1 and vp.objectid = l.id
            LEFT JOIN hotels a on vp.objecttype= 2 and vp.objectid = a.id

    ) as info

) as allinfo
WHERE RowNum >          0 AND RowNum <=         50
GROUP BY vendortitle,RowNum, id

Upvotes: 0

Views: 32

Answers (1)

Lamak
Lamak

Reputation: 70638

There are a lot of things in your query that you don't need. The derived table isn't really needed, the DISTINCT and ROW_NUMBER either. This should do the work:

SELECT  MIN(uf.id) id,
        COALESCE(l.title, a.title) as vendortitle,
        COUNT(*) as cnt
FROM userfavorites uf
INNER JOIN vendor_photos vp 
    ON uf.objectid = vp.id 
LEFT JOIN homes l 
    ON vp.objecttype= 1 
    AND vp.objectid = l.id
LEFT JOIN hotels a 
    ON vp.objecttype = 2 
    AND vp.objectid = a.id
GROUP BY COALESCE(l.title, a.title);

Upvotes: 1

Related Questions