Need help simplifying a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help simplifying a query

Author

Topic

garlic_punisher
Starting Member

4 Posts

Posted - 2009-04-20 : 21:48:47

So the 2 tables I'm dealing with are a 'files' table which contains info for a number of files where each file can belong to one component and a 'coverage' table which contains code coverage information for each file (essentially one entry for each file for each build that code coverage information is gathered for). The original query was:

SELECT DISTINCT f.componentid,
sum(c.blocks_covered) as covered,
sum(c.blocks_total) as total,
count(*) as filecount
FROM files f JOIN coverage c (nolock) ON f.fileid = c.fileid
WHERE c.buildnum='5.0.9095.0' AND c.ishighestblockcoverage = 1
GROUP BY f.componentid order by f.componentid

which basically returns a row for each component and how many files are in each component, etc. So I want to add a column in the returned rows that counts the number of files of a component that have not been investigated yet, so the following query does what I want:

SELECT DISTINCT f.componentid,
sum(c.blocks_covered) as covered,
sum(c.blocks_total) as total,
count(*) as filecount,
(SELECT count(1) FROM files f2 JOIN coverage c2 (nolock) ON f2.fileid = c2.fileid WHERE c2.buildnum='5.0.9095.0' AND c2.ishighestblockcoverage = 1 AND(f2.isinvestigated IS NULL OR f2.isinvestigated = 0) AND f.componentid = f2.componentid) as uninvestigated
FROM files f JOIN coverage c (nolock) ON f.fileid = c.fileid
WHERE c.buildnum='5.0.9095.0' AND c.ishighestblockcoverage = 1
GROUP BY f.componentid order by f.componentid

However this seems horribly redundant, but I can't figure out how to get the 'uninvestigated' value more efficiently. Any thoughts? I feel like there should be some really simple answer that I'm missing...

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-04-20 : 22:42:03

You could use the WITH clause for the common subquery
You could count(case when f2.isinvestigated IS NULL OR f2.isinvestigated = 0 then 1 else 0 end;) on the outer query (I think).

BTW, your DISTINCT is redundant. Your use of GROUP BY means you can't get duplicates.
Congrats on remembering to ORDER BY as well as GROUP BY. Most people seem to forget that part.

garlic_punisher
Starting Member

4 Posts

Posted - 2009-04-20 : 23:00:00

What is a WITH clause? I've never seen such a thing

garlic_punisher
Starting Member

4 Posts

Posted - 2009-04-21 : 12:22:54

Anyone?

Purvi
Starting Member

3 Posts

Posted - 2009-04-22 : 02:18:24

i dont know about the with clause but i think this should be what you want, though not sure -

SELECT DISTINCT f.componentid,
sum(c.blocks_covered) as covered,
sum(c.blocks_total) as total,
count(*) as filecount,
sum(case when f.isinvestigated is null or f.isinvistigated = 0 then 0 else 1 end) investigatedcount
FROM files f JOIN coverage c (nolock) ON f.fileid = c.fileid
WHERE c.buildnum='5.0.9095.0' AND c.ishighestblockcoverage = 1
GROUP BY f.componentid order by f.componentid

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-22 : 02:27:04

Hi,
With clause is CTE i.e. common table expression.
U can define a table or resultset using with clause and can use it in subsequent queries.
This may simplify yr query.

Kunal

garlic_punisher
Starting Member

4 Posts

Posted - 2009-04-24 : 14:29:20

quote:
Originally posted by Purvi

i dont know about the with clause but i think this should be what you want, though not sure -

SELECT DISTINCT f.componentid,
sum(c.blocks_covered) as covered,
sum(c.blocks_total) as total,
count(*) as filecount,
sum(case when f.isinvestigated is null or f.isinvistigated = 0 then 0 else 1 end) investigatedcount
FROM files f JOIN coverage c (nolock) ON f.fileid = c.fileid
WHERE c.buildnum='5.0.9095.0' AND c.ishighestblockcoverage = 1
GROUP BY f.componentid order by f.componentid

This is exactly what I was looking for, thank you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources