Solved it last night
I balied out on the view and did this
<cfquery name="qryGetYearList" datasource="#request.site.dsn#">
SELECT TOP (100) PERCENT
LEFT(PublicationYear, 4) AS Year, COUNT(PBID) AS BookCount
FROM dbo.PsycBOOKS
WHERE (IsNumeric(PublicationYear) = 1)
AND (Active = 1) and category in
(SELECT category
FROM dbo.PsycBOOKS
<cfif categories is not
"">where Category in ('0'
<cfloop
list="#categories#" index="x">
,'#x#'
</cfloop>)
</cfif>
)
GROUP BY LEFT(PublicationYear, 4)
ORDER BY Year DESC
</cfquery>
--
Scott Stewart
ColdFusion Developer
SSTWebworks
4405 Oakshyre Way
Raleigh, NC. 27616
(703) 220-2835
http://www.sstwebworks.com
http://www.linkedin.com/in/sstwebworks
-----Original Message-----
From: Kris Jones [mailto:[EMAIL PROTECTED]
Sent: Saturday, September 22, 2007 8:20 AM
To: CF-Talk
Subject: Re: one for the SQL gurus
Errr, rather that should be sum(bookcount) as bookcount, not sum(PBID)
as PBID. Duh.
> SELECT LEFT(PublicationYear, 4) AS Year, sum(Bookcount) as Bookcount
Cheers,
Kris
> SELECT LEFT(PublicationYear, 4) AS Year, sum(PBID) as PBID
> You have to include category in your group by statement if you're
> selecting it. And you need it in the view because you are limiting the
> resultset based on it. Can you change your cfquery to something like:
>
> <cfquery name="qryGetYear" datasource="#request.site.dsn#">
> SELECT LEFT(PublicationYear, 4) AS Year, sum(PBID) as PBID
> FROM dbo.PsycBOOKS
> WHERE (IsNumeric(PublicationYear) = 1)
> AND (Active = 1)
> <cfif categories is not "">
> AND Category in (<cfqueryparam
> cfsqltype="integer" list="true" value="#x#" />)
> </cfif>
> GROUP BY LEFT(PublicationYear,4)
> ORDER BY Year desc
> </cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Check out the new features and enhancements in the
latest product release - download the "What's New PDF" now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289201
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
