SQL Server query with multiple select

Question

I have a query which select all car makes and count each make quantity in response

SELECT 
    q.Make, Count(q.ID)
FROM 
    (SELECT  
         cars.ID, cars.Make, cars.Model, 
         cars.Year1, cars.Month1, cars.KM, cars.VIN,
         cars.Fuel, cars.EngineCap, cars.PowerKW, 
         cars.GearBox, cars.BodyType, cars.BodyColor, 
         cars.Doors, cars.FullName, Transport.address,
         (DateDiff(second,Getdate(),cars.AuEnd)) as r, 
         cars.AuEnd, cars.BuyNowPrice, cars.CurrentPrice
     FROM 
         cars 
     LEFT JOIN 
         Transport ON Cars.TransportFrom = Transport.ID 
     WHERE 
         Active = 'True' 
         AND AuEnd > GETDATE() 
         AND year1 >= 1900 AND year1 <= 2015 
         AND Make in ('AUDi', 'AIXAM', 'ALPINA')
    ORDER BY 
         cars.make ASC, cars.model ASC 
         OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY) AS q 
GROUP BY 
    q.make ORDER BY q.make ASC;

Now I need get in result as third field each make total count without offset, so now I get result

Make   CountInResponse  
AIXAM   1                
ALPINA  1                
AUDI    48

But I need to get

Make   CountInResponse   Total
AIXAM   1                 1
ALPINA  1                 1
AUDI    48                100

I think I need something like

SELECT 
    q.Make, Count(q.ID),
    (SELECT Make, Count(ID) 
     FROM cars 
     WHERE Active = 'True' AND AuEnd > GETDATE() 
       AND year1 >= 1900 AND year1 <= 2015 
       AND Make in ('AUDI', 'AIXAM', 'ALPINA')
     GROUP BY Make) as q2
FROM 
    (SELECT              
         cars.ID, cars.Make, cars.Model, 
         cars.Year1, cars.Month1, cars.KM, cars.VIN,
         cars.Fuel, cars.EngineCap, cars.PowerKW, 
         cars.GearBox, cars.BodyType, cars.BodyColor, 
         cars.Doors, cars.FullName, Transport.address,
         (DateDiff(second,Getdate(),cars.AuEnd)) as r, 
         cars.AuEnd, cars.BuyNowPrice, cars.CurrentPrice
     FROM 
         cars 
     LEFT JOIN 
         Transport ON Cars.TransportFrom = Transport.ID 
     WHERE 
         Active = 'True' 
         AND AuEnd > GETDATE() 
         AND year1 >= 1900 AND year1 <= 2015 
         AND Make in ('AUDi', 'AIXAM', 'ALPINA')
    ORDER BY 
         cars.make ASC, cars.model ASC 
         OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY) AS q

But I get an error

Msg 116, Level 16, State 1, Line 10
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

How to write right syntax?

First you need to tell what value you have to show in q2. You are selecting two columns in q2 (ie)Make, Count(ID) you cannot do that in sql server — Pரதீப்
– Pரதீப், Commented Jul 19, 2015 at 12:47
Fisrt Count is for offseted query second for total Count without offset — Dmitrij Holkin
– Dmitrij Holkin, Commented Jul 19, 2015 at 12:48

marc_s · Accepted Answer · 2015-07-19 15:09:10Z

2

The problem is you are selecting two columns in q2 (ie) Make, Count(ID) you cannot do that in SQL Server.

Try something like this.

WITH cte AS 
( 
    SELECT    
        row_number() OVER(order by cars.make ASC,cars.model ASC) AS rn, 
        cars.id, cars.make 
    FROM      
        cars 
    LEFT JOIN 
        transport ON cars.transportfrom = transport.id 
    WHERE     
        active = 'True' 
        AND auend > getdate() 
        AND year1 >= 1900 AND year1 <= 2015 
        AND make IN ('AUDI', 'AIXAM', 'ALPINA')
) 
SELECT   
    make , 
    count(CASE WHEN RN BETWEEN 50 AND 100 THEN 1 END) AS countinresponse, 
    count(1) AS total 
FROM 
    cte 
GROUP BY 
    make

Or you need to convert the sub-query in select to correlated sub-query

SELECT   q.make, 
         Count(q.id) countinresponse, 
         ( 
                  SELECT   Count(id) 
                  FROM     cars C1 
                  WHERE    c1.id = q.id 
                  AND      active='True' 
                  AND      auend > Getdate() 
                  AND      year1 >= 1900 
                  AND      year1 <= 2015 
                  AND      make IN ('AUDi', 
                                    'AIXAM', 
                                    'ALPINA') 
                  GROUP BY make) AS total 
FROM     ( 
                   SELECT    cars.id, 
                             cars.make 
                   FROM      cars 
                   LEFT JOIN transport 
                   ON        cars.transportfrom=transport.id 
                   WHERE     active='True' 
                   AND       auend > Getdate() 
                   AND       year1 >= 1900 
                   AND       year1 <= 2015 
                   AND       make IN ('AUDi', 
                                      'AIXAM', 
                                      'ALPINA') 
                   ORDER BY  cars.make ASC, 
                             cars.model ASC offset 50 rowsfetch next 50 rows only ) AS q 
GROUP BY q.make 
ORDER BY q.make ASC;

edited Jul 19, 2015 at 15:09

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 19, 2015 at 12:54

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Royi Namir Over a year ago

Are you sure he uses 2012 ?

Dmitrij Holkin Over a year ago

Msg 8120, Level 16, State 1, Line 6 Column 'q.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Pரதீப் Over a year ago

@RoyiNamir - Yeah because he is using it in his query and the error is not aboutoffset so i guess its 2012

Pரதீப் Over a year ago

@DmitrijHolkin - thats weird q.id is present inside count aggregate. Try the other query

Dmitrij Holkin Over a year ago

Another got error Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'cars'.

|

Collectives™ on Stack Overflow

SQL Server query with multiple select

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related