#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    144
    Rep Power
    13

    Adding Labels to Row results


    Hello and thank you for reading. I created the following Query to pull the total amount of big floppy hats I sold year to date and grouped them by month.

    PHP Code:
    SELECT COUNT(*) AS count FROM Item_Plan
    WHERE YEAR
    (CreateDTTM) = YEAR(GETDATE())
    AND 
    EntryName LIKE '%Big Floppy Hats%'
    GROUP BY MONTH(CreateDTTM
    This is working as intended and producing the desired results:

    47
    47
    72
    75
    56
    69

    However, when I try to label the row based off the month it seems to balk at me.

    PHP Code:
    SELECT DATENAME(MONTH,CreateDTTM) AS monthCOUNT(*) AS count 
    FROM Item_Plan
    AND EntryName LIKE '%Big Floppy Hats%'
    AND YEAR(CreateDTTM) = YEAR(GETDATE())
    GROUP BY MONTH(CreateDTTM
    I get the following error:
    Msg 8120, Level 16, State 1, Line 1
    Column 'Item_Plan.CreateDTTM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    In my novice eyes the Item_Plan.CreateDTTM is contained in the function and the GROUP BY clause.
    Last edited by gmrstudios; June 30th, 2016 at 07:38 PM.
  2. #2
  3. Headless Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,971
    Rep Power
    9647
    The SELECT term has to be the exact same as the GROUP BY term. So choose
    Code:
    SELECT MONTH(CreateDTTM) AS month, COUNT(*) AS count  
    FROM Item_Plan 
    AND EntryName LIKE '%Big Floppy Hats%' 
    AND YEAR(CreateDTTM) = YEAR(GETDATE()) 
    GROUP BY MONTH(CreateDTTM)
    Code:
    SELECT DATENAME(MONTH,CreateDTTM) AS month, COUNT(*) AS count  
    FROM Item_Plan 
    AND EntryName LIKE '%Big Floppy Hats%' 
    AND YEAR(CreateDTTM) = YEAR(GETDATE()) 
    GROUP BY DATENAME(MONTH,CreateDTTM)
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    144
    Rep Power
    13
    Thanks requinix,

    I finally settled on:

    PHP Code:
    SELECT DATENAME(month,CreateDTTM) AS monthCOUNT(*) AS count FROM Item_Plan
    WHERE EntryName LIKE 
    '%Big Floppy Hats%'
    AND YEAR(CreateDTTM) = YEAR(GETDATE())
    GROUP BY DATENAME(month,CreateDTTM), DATEPART(month,CreateDTTM)
    ORDER BY DATEPART(month,CreateDTTM
    Weirded me out when it returned the results in alphabetical order but I realize that it was working as intended and added DATEPART(month,CreateDTTM)
    Thanks for your help.
    Last edited by gmrstudios; July 1st, 2016 at 09:13 AM.

IMN logo majestic logo threadwatch logo seochat tools logo