UNION and COUNT

[cjkenworthy]

I'm trying to return a list of distinct attributes, and a COUNT of the number of rows with that distinct attributes, this is from 2 tables with the same schema:

Results e.g.

(PackReference, Total)
ABC123 2
DEF678 1
GHI901 6

SELECT PackReference, SUM('TotalRecords') AS 'Total' FROM
(
SELECT PackReference, COUNT(*) AS 'TotalRecords' FROM table1 GROUP BY PackReference
UNION ALL
SELECT PackReference, COUNT(*) AS 'TotalRecords' FROM table2 GROUP BY PackReference
ORDER BY PackReference
)
AS Results
GROUP BY PackReference

However, the query does return a list of distinct PackReferences, but the total is 0 for each record.

I have looked at other postings on UNION and COUNT, but they only seem to concentrate on the COUNT, and not the fact that there is also a GROUP BY to include the first attribute.

Any help would be greatly appreciated.

Chris.

 

[SQLSister]

Try taking the ' off the total Record inthe sum. This query worked for me when I tested against some of my tables

 Select ProposalNumber, Sum(totalRecords) As total FROM 
( 
SELECT ProposalNumber, CAst(COUNT(*)as int) AS 'TotalRecords' FROM ProposalSections GROUP BY ProposalNumber
UNION ALL
SELECT ProposalNumber, CAst(COUNT(*) as int) FROM ProposalItems GROUP BY ProposalNumber

) 
AS Results
GROUP BY ProposalNumber
Order by ProposalNumber

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[cjkenworthy]

Cheers mate!

Seems to work, I see why the apostrophes would interfere with results, as the '' in the sub queries would make TotalRecords a comlun that can be called by the outer query. So the outer query does not need the apostrophes.

Thanks again.

Chris.

 

[SQLSister]

Right, it kept telling that I couldn't sum a varchar which 'totalrecords' is. Took me a bit to see it.

BTw you don't need to define the column name in any select ina aunion except the first one as that is the one SQL Server uses to determine the column names of the union resultset.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief