Select in a Select SUM - 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

General SQL Server Forums

New to SQL Server Programming

Select in a Select SUM

Author

Topic

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2015-01-23 : 06:50:36

I have just seen a bit of code that does query 1 below and I'm not sure what it would be called in sql to do a web search to find out more info on what it is doing.

Does anyone also know the difference between query 1 and query 2 and if no differences in results or speed why you would do it like query 1 code and not query 2?

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2015-01-23 : 06:51:35

Query 1:

SELECT p.[Product ID]
,l.[Location No.]

,ISNULL((SELECT SUM(se.Quantity) FROM [StockEntry] AS se
WHERE se.[Company No_] = c.[Company No_]
AND se.[Product ID] = p.[Product ID]
AND se.[Location No.] = l.[Location No.]),0)

,ISNULL((SELECT SUM(t.Quantity) FROM [Transfers] AS t
WHERE t.[Company No_] = c.[Company No_]
AND t.[Product ID] = i.[Product ID]
AND t.[Location No.] = l.[Location No.]),0)

FROM [Products] AS p

INNER JOIN [Locations] AS l
ON p.[Company No_] = l.[Company No_]

INNER JOIN [Company] AS c
ON p.[Company No_] = c.BI_CompanyID

WHERE
ISNULL((SELECT sum(se.Quantity) FROM [StockEntry] se
WHERE se.[Company No_] = c.[Company No_]
AND se.[Product ID] = p.[Product ID]
AND se.[Product ID] = l.[Location No.]),0) <> 0

OR

ISNULL((SELECT sum(t.Quantity) FROM [Transfers] t
WHERE t.[Company No_] = c.[Company No_]
AND t.[Product ID] = i.[Product ID]
AND t.[Transfer Location No.] = l.[Location No.]),0) <> 0
;

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2015-01-23 : 06:51:55

Query 2

SELECT p.[Product ID]
,l.[Location No.]

,ISNULL(SUM(se.Quantity),0)
,ISNULL(SUM(t.Quantity),0)

FROM [Products] AS p

INNER JOIN [Locations] AS l
ON p.[Company No_] = l.[Company No_]

INNER JOIN [Company] AS c
ON p.[Company No_] = c.[Company No_]

LEFT OUTER JOIN [StockEntry] AS se
ON se.[Company No_] = c.[Company No_]
AND se.[Product ID] = p.[Product ID]
AND se.[Location No.] = l.[Location No.]

LEFT OUTER JOIN [Transfers] AS t
ON t.[Company No_] = c.[Company No_]
AND t.[Product ID] = p.[Product ID]
AND t.[Location No.] = l.[Location No.]

GROUP BY p.[Product ID]
,l.[Location No.]

HAVING ISNULL(SUM(se.Quantity),0) <> 0 OR ISNULL(SUM(t.Quantity),0) <> 0
;

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2015-01-23 : 09:04:48

Here's Query 1 maybe easy to read on one line. It has the joins with the Select.

SELECT p.[Product ID]
,l.[Location No.]

,ISNULL((SELECT SUM(se.Quantity) FROM [StockEntry] AS se WHERE se.[Company No_] = c.[Company No_] AND se.[Product ID] = p.[Product ID] AND se.[Location No.] = l.[Location No.]),0)

,ISNULL((SELECT SUM(t.Quantity) FROM [Transfers] AS t WHERE t.[Company No_] = c.[Company No_] AND t.[Product ID] = i.[Product ID] AND t.[Location No.] = l.[Location No.]),0)

FROM [Products] AS p

INNER JOIN [Locations] AS l
ON p.[Company No_] = l.[Company No_]

INNER JOIN [Company] AS c
ON p.[Company No_] = c.BI_CompanyID

WHERE

ISNULL((SELECT sum(se.Quantity) FROM [StockEntry] se WHERE se.[Company No_] = c.[Company No_] AND se.[Product ID] = p.[Product ID] AND se.[Product ID] = l.[Location No.]),0) <> 0

OR

ISNULL((SELECT sum(t.Quantity) FROM [Transfers] t WHERE t.[Company No_] = c.[Company No_] AND t.[Product ID] = i.[Product ID] AND t.[Transfer Location No.] = l.[Location No.]),0) <> 0
;

waterduck
Aged Yak Warrior

982 Posts

Posted - 2015-01-26 : 03:55:26

query 1:
INNER JOIN [Company] AS c
ON p.[Company No_] = c.BI_CompanyID

query 2:
INNER JOIN [Company] AS c
ON p.[Company No_] = c.[Company No_]

result will be different based on the underlying data.

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2015-01-26 : 09:46:23

Sorry WaterDuck that is my fault a typo c.BI_CompanyID should be c.[Company No_].

SELECT p.[Product ID]
,l.[Location No.]

,ISNULL((SELECT SUM(se.Quantity) FROM [StockEntry] AS se WHERE se.[Company No_] = c.[Company No_] AND se.[Product ID] = p.[Product ID] AND se.[Location No.] = l.[Location No.]),0)

,ISNULL((SELECT SUM(t.Quantity) FROM [Transfers] AS t WHERE t.[Company No_] = c.[Company No_] AND t.[Product ID] = i.[Product ID] AND t.[Location No.] = l.[Location No.]),0)

FROM [Products] AS p

INNER JOIN [Locations] AS l
ON p.[Company No_] = l.[Company No_]

INNER JOIN [Company] AS c
ON p.[Company No_] = c.[Company No_]

WHERE

ISNULL((SELECT sum(se.Quantity) FROM [StockEntry] se WHERE se.[Company No_] = c.[Company No_] AND se.[Product ID] = p.[Product ID] AND se.[Product ID] = l.[Location No.]),0) <> 0

OR

ISNULL((SELECT sum(t.Quantity) FROM [Transfers] t WHERE t.[Company No_] = c.[Company No_] AND t.[Product ID] = i.[Product ID] AND t.[Transfer Location No.] = l.[Location No.]),0) <> 0
;

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2015-01-26 : 09:48:05

To me the queries look the same and would return the same results but wonder if doing the join in the SELECT SUM:

,ISNULL((SELECT SUM(se.Quantity) FROM [StockEntry] AS se WHERE se.[Company No_] = c.[Company No_] AND se.[Product ID] = p.[Product ID] AND se.[Location No.] = l.[Location No.]),0)

Has any known gain or loss over a regular join, or it is just to look a bit more fancy in the coding.

Does it have a name like a sub query with the SELECT or something?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-26 : 10:02:56

The best thing to do is look at the actual execution plans for the two queries, dig into any differences and see what they are. Are there more scans with either approach? Does one do more I/O or use more CPU time than the other?

As for readability, I prefer query 2.

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2015-01-26 : 10:26:55

Hold on I think there is a difference in my Inner Code:

In my query 2

It won't work the join [Locations] AS l

to both [StockEntry] AS se and [Transfers] AS t ... perhaps

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2015-01-26 : 10:33:25

Sorry yep it will work OK, so I'm back to the start just wondering why it would be written in a different way.

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2015-01-26 : 10:35:13

Thanks gbritton yep I prefer query 2, good idea I'll check what the execution plans do.
Just thought I maybe missing out on some new type of coding, even though I'm not at any great sql stage yet.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources