CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008select
Andrew J Winkler
Andrew J Winkler

Reputation: 465

factoring SQL code - MS SQL Server

SQL Fiddle is currently down regarding MS SQL Server code, so here is a dropbox link to a .txt containing the DDL to create the schema I'm using:

https://www.dropbox.com/s/6si4r37449q3ajb/DDL.txt?dl=0

I'm studying for an exam and am wondering if there's a more efficient way to code this. My code works, but it feels wet..

Find out the software package installed on more than one computer.

Here's my solution:

select software_packs_on_more_than_one_pc
from(
    Select software.PACK as software_packs_on_more_than_one_pc, 
    count(pc.tagnum) as num_installs_per_pack
    from software
    inner join pc on software.TAGNUM=pc.tagnum
    group by software.PACK
    ) as SubQuery
where num_installs_per_pack > 1

Upvotes: 3

Views: 410

Answers (2)

Mureinik
Mureinik

Reputation: 311808

You don't need an inner query - you could just apply the condition on the aggregate count in the having clause:

SELECT   s.pack
FROM     software s
JOIN     pc p on s.tagnum = p.tagnum
GROUP BY s.pack
HAVING   COUNT(*) > 1

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can get rid of the subquery by using HAVING to filter software packages installed on more than one computer:

SELECT
    s.PACK AS software_packs_on_than_on_pc
FROM software s
INNER JOIN pc p ON
    s.TAGNUM = p.tagnum
GROUP BY s.PACK
HAVING COUNT(p.tagnum) > 1

Note:

  • It is a good practice to use meaningful alias to your tables and columns to improve readability.

Upvotes: 2

Related Questions