hi All,
I have this query I need to return Books with its category
SELECT BOOKS.NO,
BOOKS.NAME,
BOOKS.COUNTRY,
SELECTED_CATEGORY.CATEGORY_ID,
CATEGORY.CATEGORY_NAME
FROM SELECTED_CATEGORY INNER JOIN
BOOKS ON SELECTED_CATEGORY.CATEGORY_ID = BOOKS.NO INNER JOIN
CATEGORY ON SELECTED_CATEGORY.CATEGORY_ID = CATEGORY.ID
Group by BOOKS.NO;
each Book has multiple categories , i used for that purpose the group by option in SQL but it gives me an error
Msg 8120, Level 16, State 1, Line 1
Column 'BOOKS.NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Loading
HanookPosted Jan 9, 2014, 6:54 AM
BOOKS.NAME,
BOOKS.COUNTRY,
SELECTED_CATEGORY.CATEGORY_ID,
CATEGORY.CATEGORY_NAME
FROM SELECTED_CATEGORY INNER JOIN
BOOKS ON SELECTED_CATEGORY.CATEGORY_ID = BOOKS.NO INNER JOIN
CATEGORY ON SELECTED_CATEGORY.CATEGORY_ID = CATEGORY.ID
Group by BOOKS.NO,
BOOKS.NAME,
BOOKS.COUNTRY,
SELECTED_CATEGORY.CATEGORY_ID,
CATEGORY.CATEGORY_NAME;
I doubt that the highlighted part is causing duplicates.... What is the referencing column between Selected_Category and Books tables? I guess that should be as follows
BOOKS ON SELECTED_CATEGORY.CATEGORY_ID = BOOKS.CategoryReferencingColumnName
Note: If you are not getting correct result, post us back the sample data and expected output
ToBePosted Jan 9, 2014, 12:50 AM
venkata kumarPosted Jan 8, 2014, 7:21 AM
try the fallowing
SELECT BOOKS.NO,
BOOKS.NAME,
BOOKS.COUNTRY,
SELECTED_CATEGORY.CATEGORY_ID,
CATEGORY.CATEGORY_NAME
FROM SELECTED_CATEGORY INNER JOIN
BOOKS ON SELECTED_CATEGORY.CATEGORY_ID = BOOKS.NO INNER JOIN
CATEGORY ON SELECTED_CATEGORY.CATEGORY_ID = CATEGORY.ID
Group by BOOKS.NO,
BOOKS.NAME,
BOOKS.COUNTRY,
SELECTED_CATEGORY.CATEGORY_ID,
CATEGORY.CATEGORY_NAME;
Dhirendra MisraPosted Jan 8, 2014, 5:07 AM
Your query is incorrect.
Group By : The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
If you want more than one column, mentions all columns which you have used in select list.
Like example:
SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders
INNER JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID)
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY ShipperName,LastName;
Hope it makes you clear.
for more details refer the link:
http://beginner-sql-tutorial.com/sql-group-by-clause.htm
http://www.tizag.com/sqlTutorial/sqlgroupby.php
Abhay ShankerPosted Jan 8, 2014, 4:51 AM