I’m getting ‘Column name is invalid in the select list’ error when i execute the query

by

Try this :

SELECT        TOP (3) a.INCIDENT_ID, a.REFERENCE, SUM(b.COST) AS TOTAL_COST
FROM          FDDC_T_D_INCIDENT AS a INNER JOIN
              FDDC_T_D_INCIDENT_COST AS b ON a.INCIDENT_ID =b.FINCIDENT_ID
WHERE        (a.CREATED_DATE >= @date1) AND (a.CREATED_DATE <= @date2)
GROUP BY a.INCIDENT_ID, a.REFERENCE
ORDER BY TOTAL_COST DESC

You need to add ‘a.REFERENCE’ to the GROUP BY clause.

EDIT :

Please try this if you only want to GROUP BY ‘a.INCIDENT_ID’ :

SELECT INCIDENT_ID, REFERENCE, TOTAL_COST
FROM(
        SELECT        TOP (3) a.INCIDENT_ID, a.REFERENCE, SUM(b.COST) AS TOTAL_COST
        FROM          FDDC_T_D_INCIDENT AS a INNER JOIN
                      FDDC_T_D_INCIDENT_COST AS b ON a.INCIDENT_ID =b.FINCIDENT_ID
        WHERE        (a.CREATED_DATE >= @date1) AND (a.CREATED_DATE <= @date2)
        GROUP BY a.INCIDENT_ID, a.REFERENCE
        ORDER BY TOTAL_COST DESC
    ) AS T
GROUP BY INCIDENT_ID

Hope this helps.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. select yesterday date in where condition
  3. How to join 3 and more tables in SQL?
  4. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  5. Is there a combination of “LIKE” and “IN” in SQL?
  6. SQL query to select dates between two dates
  7. What is the benefit of using “SET XACT_ABORT ON” in a stored procedure?
  8. is there an advantage to varchar(500) over varchar(8000)?
  9. Selecting COUNT(*) with DISTINCT
  10. Effect of NOLOCK hint in SELECT statements
  11. What is best tool to compare two SQL Server databases (schema and data)? [duplicate]
  12. Calculating number of full months between two dates in SQL
  13. What is the difference between using a cross join and putting a comma between the two tables?
  14. Replacing NULL with 0 in a SQL server query
  15. How do I calculate a running total in SQL without using a cursor?
  16. Equivalent of Oracle’s RowID in SQL Server
  17. Concat field value to string in SQL Server
  18. Months between two dates
  19. Why can’t I use alias in a count(*) “column” and reference it in a having clause?
  20. How to find third or nᵗʰ maximum salary from salary table?
  21. SQL “between” not inclusive
  22. Difference of two date time in sql server
  23. Benefits Of Using SQL Ordinal Position Notation?
  24. sql group by only rows which are in sequence
  25. SQL Server replace, remove all after certain character
  26. Does UNION ALL guarantee the order of the result set [duplicate]
  27. Inserting rows into a table with one IDENTITY column only [duplicate]
  28. How to find sum of multiple columns in a table in SQL Server 2005?
  29. How to retrieve field names from temporary table (SQL Server 2008)
  30. SQL query to group by day

Leave a Comment