Subselect & Union - 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)

Subselect & Union

Author

Topic

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-20 : 22:14:26

Hi,

I have a query which contains 2 subselects joined with a union all. The select for each is just a count, so I'm only returning 2 rows. I then want to be able to perform a calculation between these 2 results... ie divide one by the other to get the percentage.

The only way I could think of doing that was make the whole query a subselect of another query where I could then perform the calculation in the new select statement, however it doesn't like this. I just get incorrect syntax near the closing bracket of the from section.

Any ideas? Thanks!

SELECT *
FROM

(SELECT count(t0.product)
FROM
(SELECT t0.packslip ,
t1.date_upld ,
t0.product AS product ,
t0.qty_topick as topick ,
t0.qty_picked as picked ,
t0.qty_topick - t0.qty_picked as shorted,
(t0.qty_picked / t0.qty_topick) * 100 as linefill
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101)) t0

UNION ALL

SELECT count(t1.product)
FROM
(SELECT t0.packslip ,
t1.date_upld ,
t0.product AS product ,
t0.qty_topick as topick ,
t0.qty_picked as picked ,
t0.qty_topick - t0.qty_picked as shorted,
(t0.qty_picked / t0.qty_topick) * 100 as linefill
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101)
AND t0.qty_picked <> t0.qty_topick) t1) t2

dshelton
Yak Posting Veteran

73 Posts

Posted - 2008-05-20 : 22:29:38

How about this:

SELECT count(t0.product) t0_Count, sum(CASE WHEN t0.qty_picked <> t0.qty_topick THEN 1 ELSE 0 END) t1_Count
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101)

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-20 : 23:32:36

I did try a case statement with it at one point but didn't work either.... this one works like a dream! Thanks heaps!

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-20 : 23:39:07

Except.... how do I use the result of the case statement in a calculation?

I tried
(SUM(
CASE
WHEN t0.qty_picked = t0.qty_topick
THEN 1
ELSE 0
END))/(COUNT(t0.product))

but returns 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-21 : 00:10:45

quote:
Originally posted by michaelb

Except.... how do I use the result of the case statement in a calculation?

I tried
(SUM(
CASE
WHEN t0.qty_picked = t0.qty_topick
THEN 1
ELSE 0
END))* 1.0/(COUNT(t0.product))

but returns 0

May be its converting the result to an integer value. Change like this and try

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-21 : 00:22:38

Perfect!! Thanks!

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-21 : 01:29:52

Stuck again...

I now want to only count items that are the responsibility of a particular purchasing officer. This detail is stored in comparison.dbo.vlgxplc in field u_vlgx_plc. Using the below SQL all I get is 0 and NULL. The red writing is what I added.

SELECT COUNT(t0.product) AS 'Total Lines',
SUM(
CASE
WHEN t0.qty_picked = t0.qty_topick
THEN 1
ELSE 0
END) AS 'Filled Lines',
SUM(
CASE
WHEN t0.qty_picked <> t0.qty_topick
THEN 1
ELSE 0
END) AS 'Shorted Lines',
(SUM(
CASE
WHEN t0.qty_picked = t0.qty_topick
THEN 1
ELSE 0
END))* 1.0/(COUNT(t0.product)) AS 'Order Fill'

FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
LEFT JOIN comparison.dbo.vlgxplc t2
ON t1.product = t2.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101)
AND t2.u_vlgx_plc = 'BF'

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-21 : 01:35:13

quote:
Originally posted by michaelb

Stuck again...

I now want to only count items that are the responsibility of a particular purchasing officer. This detail is stored in comparison.dbo.vlgxplc in field u_vlgx_plc. Using the below SQL all I get is 0 and NULL. The red writing is what I added.

SELECT COUNT(t0.product) AS 'Total Lines',
SUM(
CASE
WHEN t0.qty_picked = t0.qty_topick
THEN 1
ELSE 0
END) AS 'Filled Lines',
SUM(
CASE
WHEN t0.qty_picked <> t0.qty_topick
THEN 1
ELSE 0
END) AS 'Shorted Lines',
(SUM(
CASE
WHEN t0.qty_picked = t0.qty_topick
THEN 1
ELSE 0
END))* 1.0/(COUNT(t0.product)) AS 'Order Fill',
SUM(CASE WHEN t2.itemcode IS NOT NULL THEN 1 ELSE 0 END) AS SupervisorCount

FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
LEFT JOIN comparison.dbo.vlgxplc t2
ON t1.product = t2.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101)
AND t2.u_vlgx_plc = 'BF' OR t2.u_vlgx_plc IS NULL

Any ideas?

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-21 : 01:39:09

That one hugely increases the count figures it returns, and returns 0 as supervisor count. thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-21 : 01:44:47

quote:
Originally posted by michaelb

That one hugely increases the count figures it returns, and returns 0 as supervisor count. thanks.

Do you have any records in comparison.dbo.vlgxplc that satisfies
condition t1.product = t2.itemcode ? also is the relationship 1 to many?

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-21 : 02:08:38

t1.product always equals t2.itemcode.

It was a one to many relation in the original table but I moved them all to a single table so it is now a one to one relation.... I have done your query with an inner join instead of left an dnow it returns all 0 and NULL.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-21 : 03:48:09

quote:
Originally posted by michaelb

t1.product always equals t2.itemcode.

It was a one to many relation in the original table but I moved them all to a single table so it is now a one to one relation.... I have done your query with an inner join instead of left an dnow it returns all 0 and NULL.

thanks

In that case i dont think your count figures will skew.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources