Count 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)

Count Query

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-26 : 05:53:35

Hi

I have two tables, Sales Headers and SalesLines. The SalesHeaders table will hold basic details of a sale, and the Sales Lines table will hold details of the items in the sale.

An example of tables are as follows:

SalesHeaders:
Sales_Ref, OrderDate, Customer_No
111,01/05,2008,301
112,01/05/2008,333
113,01/05/2008,309
114,03/05/2008,306
115,03/05/2008,309
...

SalesLines:
Sales_Ref, PartNumber, Qty
111,e4334,3
111,d434,1
111,r555,20
112,r332,1
113,f442,5
114,d332,2
114,s324,7
114,s432,5
114,l567,2
115,p098,10

What I am trying to achieve is finding a count of SaleHeaders where it has only 1 item in the SalesLines table. ( I hope this makes sense)

I tried using the follwoing query:

SELECT
sh.[Sales_Ref]
,COUNT(sl.[Sales_Ref])

FROM dbo.SalesLines AS sl
LEFT JOIN dbo.SalesHeaders AS sh ON sl.[Sales_Ref] = sh.[Sales_Ref]

GROUP BY
sh.[Sales_Ref]
HAVING COUNT(sl.[Sales_Ref) = 1

But this returned all the record with a count of 1. Is it possible to just return the number?

Thanking you in Advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 06:08:02

[code]SELECT COUNT(*)
FROM
(
SELECT sh.Sales_Ref
FROM SalesHeaders sh
INNER JOIN SalesLines sl
ON sl.Sales_Ref=sh.Sales_Ref
GROUP BY sh.Sales_Ref
HAVING COUNT(*) >1
)t[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 06:09:13

Why join at all? If you want all associated records with a number of 1, they already exists on detailed table, as well as master table.

DECLARE	@Sample TABLE (Ref INT, PartNumber INT, Qty INT)

INSERT	@Sample
SELECT	111, 434,  3 UNION ALL
SELECT	111, 434,  1 UNION ALL
SELECT	111, 555, 20 UNION ALL
SELECT	112, 332,  1 UNION ALL
SELECT	113, 442,  5 UNION ALL
SELECT	114, 332,  2 UNION ALL
SELECT	114, 324,  7 UNION ALL
SELECT	114, 432,  5 UNION ALL
SELECT	114, 567,  2 UNION ALL
SELECT	115, 098, 10

SELECT	SUM(Items) AS Yak
FROM	(
		SELECT		1 AS Items
		FROM		@Sample
		GROUP BY	Ref
		HAVING		COUNT(*) = 1
	) AS d

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-26 : 06:20:18


SELECT	SUM(1) AS Yak
FROM	(
		SELECT		1 AS Items
		FROM		@Sample
		GROUP BY	Ref
		HAVING		COUNT(*) = 1
	) AS d

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources