Error in grouping the clause

VIVEKANANDHAN_PERIASAMY
VIVEKANANDHAN_PERIASAMY used Ask the Experts™
on
I added new column (propertyname) to the result and got below errors.

Msg 8120, Level 16, State 1, Line 2
Column 'Property.PropertyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8155, Level 16, State 2, Line 24
No column name was specified for column 2 of 't'.
Msg 8155, Level 16, State 2, Line 24
No column name was specified for column 3 of 't'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'Ticket Title'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Ticket Title'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'cnt'.



Query Used:

select "Ticket Title",propertyname, sum(cnt)As "Total Count" from (
SELECT propertyname,SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000) "Ticket Title",
COUNT (*) cnt FROM CT a inner join Property b on a.PropertyId=b.PropertyId
WHERE PropertyName like '%abc%'
and a.CreatedDtim >='2012-03-11' and a.CreatedDtim >='2012-03-14'
and PATINDEX ('%Event[0-9]%', ticketdesc) > 0
And CHARINDEX (';  Desc:', ticketdesc) > 0
Group by SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000)
     
  union
      SELECT propertyname, SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
      CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000),
      COUNT (*)
            FROM T a inner join Property b on a.PropertyId=b.PropertyId
WHERE PropertyName like '%abc%'
and a.CreatedDtim >='2012-03-11' and a.CreatedDtim >='2012-03-14' --Need to give propertyid,start and end date time
and PATINDEX ('%Event[0-9]%', ticketdesc) > 0
And CHARINDEX (';  Desc:', ticketdesc) > 0
Group by SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
      CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000)
) t
group by "Ticket Title" ,propertyname order by "Total Count" desc
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
try this:


select  
propertyname,
[Ticket Title],
sum(cnt)As "Total Count"
from (
      SELECT
propertyname,
SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000) [Ticket Title],
COUNT (*) cnt
FROM
CT a
inner join
Property b
on a.PropertyId=b.PropertyId
WHERE PropertyName like '%abc%'
and a.CreatedDtim >='2012-03-11' and a.CreatedDtim >='2012-03-14'
and PATINDEX ('%Event[0-9]%', ticketdesc) > 0
And CHARINDEX (';  Desc:', ticketdesc) > 0
Group by
propertyname,SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000)
     
  union

      SELECT
propertyname,
SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000) [Ticket Title],
 COUNT (*) cnt
FROM
T a
inner join
Property b
on a.PropertyId=b.PropertyId
WHERE PropertyName like '%abc%'
and a.CreatedDtim >='2012-03-11' and a.CreatedDtim >='2012-03-14' --Need to give propertyid,start and end date time
and PATINDEX ('%Event[0-9]%', ticketdesc) > 0
And CHARINDEX (';  Desc:', ticketdesc) > 0
Group by propertyname,SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000)
) t
group by propertyname ,[Ticket Title], order by 3 desc

Commented:
Hello,

25112 fixed the core issues with the original post (no aliases are allowed in ORDER BY and GROUP BY clauses, and once you have defined an alias you can not refer to it as a character string using quotes).  I do see 3 items that will throw off 25112's code if you copy-paste it:

First, the line {sum(cnt)As "Total Count"}  needs a space after the ) and before the AS or you'll confuse the system.  The original code would be more readable with AS used for identifying all aliases.

Second, there's an alias in each of the unioned tables / queries that is no longer the text string it needs to be (from 25112's code):
CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000) [Ticket Title],

Needs to be:
CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000) AS "Ticket Title",

Make sure to fix both instances.

Third, there's an extra comma in the last line:
group by propertyname ,[Ticket Title], order by 3 desc

After [Ticket Title] there should not be a comma, as the ORDER BY is not part of a list (could move it to the next line for readability).

The ORDER BY at the end could also have sum(cnt) instead of just 3.  The 3 will work, but if you add or remove fields the count can get off.
ORDER BY sum(cnt) DESC

Thanks,
Chris
Top Expert 2012
Commented:
no aliases are allowed in ORDER BY
Actually aliases are perfectly legal in an ORDER BY clause and in fact the only way to do it (other than using the ordinal position) when involved in a UNIONd statement.

See here from SQL Server BOL (my emphasis):
order_by_expression
Specifies a column on which to sort. A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name or alias in the select list. An integer cannot be specified when the order_by_expression appears in a ranking function. A sort column can include an expression, but when the database is in SQL Server (90) compatibility mode, the expression cannot resolve to a constant. Column names and aliases can be qualified by the table or view name. In SQL Server, qualified column names and aliases are resolved to columns listed in the FROM clause. If order_by_expression is not qualified, the value must be unique among all columns listed in the SELECT statement.

Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set.

The ORDER BY clause can include items that do not appear in the select list. However, if SELECT DISTINCT is specified, or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.

Additionally, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list.

Commented:
My appologies, I mixed my GROUP BY and ORDER BY rules...
Top Expert 2012

Commented:
VIVEKANANDHAN_PERIASAMY,

You appear to have awarded me the points in error.  Please request in Community Support that the question be re-opened so that you can correct this.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial