[Sql-ig] GROUP BY problem
Nellhaus, Tobin
tobin.nellhaus at yale.edu
Thu Sep 8 13:27:11 EDT 2011
Hi all,
I guess at this point moving the SQL group along has fallen to me, but I think it would be a lot better to have someone else in charge (or at least in co-charge). Any volunteers?
In the meantime, I'm having a problem with a query (below). I have a version that gives me the total circulations of books in Film Studies from 2006 through 2010, broken down into English and Foreign Language. I'm now trying to get a further breakdown (going across the X axis) by year. But the query is telling me that I don't have a correct GROUP BY expression. Any ideas?
Thanks,
Tobin Nellhaus
Librarian for Performing Arts, Media and Philosophy
Coordinator for Humanities Collection Development
tobin.nellhaus at yale.edu
203/432-8212
==========
SELECT Language
, (CASE Circ_Year
WHEN '2006' THEN count(Language)
ELSE 0
END) Circ_2006
, (CASE Circ_Year
WHEN '2007' THEN count(Language)
ELSE 0
END) Circ_2007
, (CASE Circ_Year
WHEN '2008' THEN count(Language)
ELSE 0
END) Circ_2008
, (CASE Circ_Year
WHEN '2009' THEN count(Language)
ELSE 0
END) Circ_2009
, (CASE Circ_Year
WHEN '2010' THEN count(Language)
ELSE 0
END) Circ_2010
FROM
(
SELECT (CASE BIB_TEXT.LANGUAGE
WHEN 'eng' THEN 'English'
ELSE 'Foreign'
END ) AS Language
, TO_CHAR(CIRC_TRANS_ARCHIVE.CHARGE_DATE, 'YYYY') AS Circ_Year
FROM BIB_TEXT
, BIB_ITEM
, ITEM
, MFHD_ITEM
, MFHD_MASTER
, CIRC_TRANS_ARCHIVE
WHERE
item.item_id = mfhd_item.item_id
AND mfhd_item.mfhd_id = mfhd_master.mfhd_id
AND ( ( substr(MFHD_MASTER.DISPLAY_CALL_NO,1,6) BETWEEN 'PN1993' AND 'PN1999')
OR ( substr(MFHD_MASTER.DISPLAY_CALL_NO,1,9) BETWEEN 'PN1992.93' AND 'PN1992.99')
)
AND ITEM.ITEM_ID = BIB_ITEM.ITEM_ID
AND BIB_ITEM.BIB_ID = BIB_TEXT.BIB_ID
AND ITEM.ITEM_ID = CIRC_TRANS_ARCHIVE.ITEM_ID
AND TO_CHAR(CIRC_TRANS_ARCHIVE.CHARGE_DATE, 'YYYY') BETWEEN '2006' AND '2010'
AND CIRC_TRANS_ARCHIVE.PATRON_GROUP_ID IN ('4', '5', '9')
)
GROUP BY Language
ORDER BY Language
More information about the Sql-ig
mailing list