Maybe this?

select sum(mycount)

from (

select count(*) as mycount

from partno_table

group by partno

)x

Ideally you should provide table definitions for all relevant tables along with sample data in the format

CREATE TABLE dbo.MILEAGE(ZIPCODEFROM VARCHAR(.... )

INSERT INTO dbo.MILEAGE ...

You'll usually get more responses. Meanwhile, here's...

DerbyNeal (3/3/2011)

---

Hi Mark,

Thanks - Firstly, the structure is perfect.

However, the 'Postcode' values aren't being pulled from the Mileage table though. The output contains the values that I gave in...

See if this helps

DECLARE @MILEAGE TABLE(ZIPCODEFROM VARCHAR(30),ZIPCODETO VARCHAR(30), ID VARCHAR(30), NAME VARCHAR(30), RIGIDACC VARCHAR(30))

INSERT INTO @MILEAGE(ZIPCODEFROM,ZIPCODETO,ID,NAME,RIGIDACC)

SELECT 'XX16 6DN',NULL,'start','Start',1 UNION ALL

SELECT NULL,'ZZ21 2EU','end','End',NULL ;

WITH Unpivotted (ZIPCODEFROM ,ZIPCODETO,Value,Name) AS (

SELECT ZIPCODEFROM...

SELECT testId,testVersion,

MAX(CASE WHEN AdminType='CAB' THEN StatusId END) AS CAB,

MAX(CASE WHEN AdminType='RM' THEN StatusId END) AS RM,

...

Try this

CREATE TABLE #Data(ShortDate DATETIME,FlowDirection INT)

INSERT INTO #Data(ShortDate,FlowDirection)

SELECT '20010101', 0 UNION ALL

SELECT '20010102', 1 UNION ALL

SELECT '20010103', 1 UNION ALL

SELECT '20010104', 0 UNION ALL

SELECT '20010105', -1 UNION ALL

SELECT '20010106', 0...

Another way

WITH Starts AS (

SELECT a.clientID, a.lastName, a.firstName, a.startDate

FROM #temp a

WHERE NOT EXISTS (SELECT * FROM #temp b

...

Should be enough to get you started...

WITH Summary AS (

SELECT Username ,

SUM(CASE WHEN PositionTypeCode = 'AMP' THEN 1 ELSE 0 END) AS AMP,

...

Ninja's_RGR'us (1/21/2011)

---

CELKO (1/21/2011)

---

You better off doing the running totals in the front end in T-SQL. Do you have a report writer of some kind?

In Standard SQL there is an...

dibbydibby (1/18/2011)

---

Yep as stated I have tried that but the order by at the end still causes a SORT to carried out for each query either side of the UNION...

Try UNION ALL instead of UNION

SELECT CONVERT(CHAR(10),s.Date,120) AS Date,

CONVERT(CHAR(5),s.Time,108) AS StarTime,

CONVERT(CHAR(5),e.Time,108) AS EndTime,

s.CardNumber,

...

Changing

value('.','varchar(max)'),1,1,'')

to

value('(./text())[1]','varchar(max)'),1,1,'')

appears to give a better query plan

Great article BTW.

Adi Cohn-120898 (1/10/2011)

---

Since you use different tags, I think that you can do it only with union all query. If you can modify the XML so the tag’s name...

Try this. You could also use the 'quirky update' method from here

http://www.sqlservercentral.com/articles/T-SQL/68467/

which would be very fast.

WITH CTE1 AS (

SELECT tradeId,

tradeClosedDateTime,

...