[Solved] Comparing two columns in SQL Server

EverSQL Database Performance Knowledge Base

Comparing two columns in SQL Server

Database type:

It's a bit tough to explain, but will illustrate by below tables. 

ID   names     pages
1   maths_1      2  
1   maths_2      2
2   history      1
3   economics    1
4   physics      1

The table shows book_id and book_name and total pages it has

So total maths book has 2 pages

My question is: if maths_3 is there in the table. if the table exits like below

ID   names     pages
 1   maths_1     2  
 1   maths_2     2
 1   maths_3     2
 2   history     1
 3   economics   1
 4   physics     1

there is one more page called maths_3 but the count still has 2 pages. In this case, I want output something like below which will be easy for me to pick the odd on out

Desired output:

  ID   names   pages  difference
   1    3       2        1
   2    1       1        0
   3    1       1        0
   4    1       1        0

Table name : BOOK

I used this query just to get the counts but can anyone help to get the difference

select 
    id, count(names) pagesbynames,
    MAX(pages) pagesentered 
from 
    book 
group by 
    id

From above query I get this output:

ID   pagesbynames   pagesentered  
 1        3               2    
 2        1               1    
 3        1               1     
 4        1               1

Stuck here, please someone help me to get the desired output

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX book_idx_id ON book (id);
The optimized query:
SELECT
        book.id,
        count(book.names) pagesbynames,
        MAX(book.pages) pagesentered 
    FROM
        book 
    GROUP BY
        book.id

Related Articles



* original question posted on StackOverflow here.