I've got a query issue that's driving me crazy
Here's the query, actually it's a SQL server view
SELECT TOP (100) PERCENT LEFT(PublicationYear, 4) AS Year, COUNT(PBID) AS
BookCount,Category
FROM dbo.PsycBOOKS
WHERE (IsNumeric(PublicationYear) = 1)
AND (Active = 1)
GROUP BY LEFT(PublicationYear, 4), Category
ORDER BY Year DESC
What this returns is
2007 | 14 | C
2007 | 2| A
What I need is
2007 | 16 |
If I pull category out of the group by it errors out.
I can't remove the Category because of this:
<cfquery name="qryGetYear" datasource="#request.site.dsn#">
SELECT LEFT(PublicationYear, 4) AS Year, PBID, Category
FROM dbo.PsycBOOKS
WHERE (IsNumeric(PublicationYear) = 1)
AND (Active = 1)
<cfif categories is not "">
and Category in ('0'
<cfloop list="#categories#"
index="x">
,'#x#'
</cfloop>
)
</cfif>
Order By Year desc
</cfquery>
The category is passed to the query from a form. It can be a,c or e or any
combination
Any ideas?
Thanks
sas
--
Scott Stewart
ColdFusion Developer
SSTWebworks
4405 Oakshyre Way
Raleigh, NC. 27616
(703) 220-2835
http://www.sstwebworks.com
<http://www.linkedin.com/in/sstwebworks>
http://www.linkedin.com/in/sstwebworks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion is delivering applications solutions at at top companies
around the world in government. Find out how and where now
http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289161
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
