help with 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)

help with query

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-09-20 : 08:53:04

hi i need help how i count ..?

SELECT
COUNT(OrderNo)
--> COUNT(OrderNo) WHERE driver<>0 --how i write it in the query?
FROM Orders

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-09-20 : 08:54:45

SELECT
COUNT(OrderNo)
FROM Orders
having driver<>0

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 09:28:37

nope. Having filters results AFTER an aggregate (GROUP BY)

The query should probably be very close to the original post:


SELECT
    COUNT(OrderNo)
FROM
    Orders
WHERE
    driver <> 0

inbs -- if this isn't what you want please show us some sample data and expected result.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-09-20 : 10:18:24

i need to 2 count
first is count all ordres
second is count orders that haven't driver (driver<>0)

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-09-20 : 10:24:25

sample data and expected output pls

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 10:25:11

try this



-- all the orders
SELECT
	'All Orders' AS [Legend]
	, COUNT([orderNo]) AS [Orders]
FROM
	[order]

-- all the orders with driver <> 0
UNION SELECT
	'Driver <> 0 Orders' AS [Legend]
	, COUNT([orderNo]) As [Orders]
FROM
	[order]
WHERE
	[driver] <> 0

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 10:27:11

Example:





DECLARE @order TABLE (
	[orderNo] VARCHAR(255)
	, [driver] INT
	)

INSERT @order ([orderNo], [driver])
      SELECT '0A21', 1
UNION SELECT '01123', 0
UNION SELECT '01$ 21', 0
UNION SELECT '01AAa', 1
UNION SELECT '01454', 0
UNION SELECT '011', 1
UNION SELECT '0132', 0

-- all the orders
SELECT
	'All Orders' AS [Legend]
	, COUNT([orderNo]) AS [Orders]
FROM
	@order

-- all the orders with driver <> 0
UNION SELECT
	'Driver <> 0 Orders' AS [Legend]
	, COUNT([orderNo]) As [Orders]
FROM
	@order
WHERE
	[driver] <> 0

Results:



Legend             Orders
------------------ -----------
All Orders         7
Driver <> 0 Orders 3

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-20 : 10:33:45

quote:

hi i need help how i count ..?

SELECT
COUNT(OrderNo)
--> COUNT(OrderNo) WHERE driver<>0 --how i write it in the query?
FROM Orders

SELECT
COUNT(OrderNo) as total, (select COUNT(OrderNo) WHERE driver<>0) as subtotal
FROM Orders

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 10:42:00

quote:
Originally posted by namman

quote:

hi i need help how i count ..?

SELECT
COUNT(OrderNo)
--> COUNT(OrderNo) WHERE driver<>0 --how i write it in the query?
FROM Orders

SELECT
COUNT(OrderNo) as total, (select COUNT(OrderNo) FROM Orders WHERE driver<>0) as subtotal
FROM Orders

Needed the table in the subquery. Try and stay away from subqueries though. It's fine for this situation but they have a habit of migrating to other code and becoming RBAR horrors. (row by agonizing row)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources