CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

SQL SERVER count distinct using Offset

With this query:

SELECT ID,Make,Model 
FROM cars 
WHERE active='true'  
ORDER BY make ASC, model ASC 
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

I got this result:

ID          Make         Model
26800911    AIXAM       CROSSLINE
26796643    AIXAM       PREMIUM
26700448    ALFA ROMEO  145
26796420    ALFA ROMEO  147
26480721    ALFA ROMEO  147

How to write query to get from this result Count of Each make? So i need to have AIXAm = 2, Alfa Romeo = 3

Thoose one return all result without Offset Limit

SELECT make,SUM(CASE WHEN active='true' THEN 1 ELSE 0 END) 
FROM cars 
WHERE active='true' 
GROUP BY make 
ORDER BY make ASC;

AND

SELECT make,Count(make) 
FROM cars 
WHERE active='true' 
GROUP BY make 
ORDER BY make ASC;

AND

SELECT TOP 5 make,SUM(CASE WHEN active='true' THEN 1 ELSE 0 END) 
FROM cars 
WHERE active='true' 
GROUP BY make 
ORDER BY make ASC;

All return not what i need

AIXAM       2
ALFA ROMEO  106
ALPINA      1
AUDI        686
BMW         557

Upvotes: 2

Views: 646

Answers (1)

sblandin
sblandin

Reputation: 964

You can try with a subquery:

SELECT q.Make, Count(q.ID)
FROM
(
SELECT ID,Make,Model 
FROM cars 
WHERE active='true'  
ORDER BY make ASC, model ASC 
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
) AS q
GROUP BY q.make
ORDER BY q.make ASC;

Upvotes: 3

Related Questions