[Sql-ig] GROUP BY problem

[Ji, Yue]

Hi Tobin,

You missed the field "Circ_Year" in the "group by" segment.
In you "select" segment, you have two fields: Language and Circ_Year. The "group by" needs all fields from the "select" segment.

An example you should change in your "group by" is this:
GROUP BY Language,Circ_Year
ORDER BY Language,Circ_Year

Yue
-----Original Message-----
From: sql-ig-bounces at mailman.yale.edu [mailto:sql-ig-bounces at mailman.yale.edu] On Behalf Of Nellhaus, Tobin
Sent: Thursday, September 08, 2011 1:27 PM
To: SQL Interest Group (sql-ig at mailman.yale.edu)
Subject: [Sql-ig] GROUP BY problem

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
_______________________________________________
Sql-ig mailing list
Sql-ig at mailman.yale.edu
http://mailman.yale.edu/mailman/listinfo/sql-ig