[Sql-ig] GROUP BY problem

Nellhaus, Tobin tobin.nellhaus at yale.edu
Fri Sep 9 09:10:34 EDT 2011


Interesting!  I had a previous attempt which used "count(Circ_Year)" instead of "count(Language)" in the SELECT statement's CASE sections, but that didn't work either.  The data resulting from the revised query looks correct, but the format isn't what I hoped for, so there's still some tweaking for me to do.

Thanks,

Tobin Nellhaus
Librarian for Performing Arts, Media and Philosophy
Coordinator for Humanities Collection Development
tobin.nellhaus at yale.edu
203/432-8212



> -----Original Message-----
> From: Ji, Yue
> Sent: Thursday, September 08, 2011 2:38 PM
> To: Nellhaus, Tobin; SQL Interest Group (sql-ig at mailman.yale.edu)
> Subject: RE: GROUP BY problem
> 
> 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


More information about the Sql-ig mailing list