VIVEKANANDHAN_PERIASAMY
asked on
Error in grouping the clause
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My appologies, I mixed my GROUP BY and ORDER BY rules...
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.
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.
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