SELECT-Group by and Order by,Having Clause,count(),Joins

Sat Apr 07, 2007 6:12 pm

DML Statements(Advanced)

sql code
SELECT <column list>
FROM <table names>
[WHERE <condition>]
[GROUP BY <column list>]
[HAVING <condition>]
[ORDER BY <column list>]

1. Using Group by and Order by

Example 1: (without aggregate function equivalent to using distinct)

Give all different unit prices from the product table ordered Ascendingly.

sql code
SELECT unitprice
FROM products
GROUP BY unitprice
ORDER BY unitprice Asc;

Example 2:

For each unit price, find the number of products having that unit price ordered Descendingly by unit price with a not null unit price.

sql code
SELECT unitprice, count(*)
FROM products
WHERE unitprice <> NULL
GROUP BY unitprice
ORDER BY unitprice Desc;

Count is a Group Function , search for others and see example(s) on them? "Grouping columns".

2.Using Having Clause

Example 1:

What are the cities where more than 1 employee live?

sql code
FROM employees
HAVING count (employeeid) >1;

Example 2:

Get the id of the customer that ordered more than one order

sql code
SELECT customerid , count (*) [as Orders]
FROM orders
GROUP BY customerid
HAVING count (*) >1;


How about making example that get you name of customers CompanyName. who have more than one order?

sql code
Select Customers.companyName as Name, count(Orders.Customerid) as Orders
From Orders , Customers
where Orders.Customerid = Customers.Customerid
group by (Customers.companyName);

Example 1:

Get the names, prices and supplier names for each product

sql code
SELECT  Products.ProductName,Suppliers.ContactName,Products.UnitPrice
FROM Products, Suppliers
WHERE Products.SupplierID = Suppliers.SupplierID

Example 2:

State the supplier names for each category.

sql code
SELECT Categories.CategoryName, Suppliers.CompanyName
FROM Categories,Products, Suppliers
WHERE Suppliers.SupplierID = Products.SupplierID
AND Products.CategoryID = Categories.CategoryID;

Re: SELECT-Group by and Order by,Having Clause,count(),Joins

Thu May 09, 2013 10:56 pm


  Related Posts  to : SELECT-Group by and Order by,Having Clause,count(),Joins
 SQL GROUP BY Command     -  
 Cannot Delete Computer group     -  
 The New Indian Express Group Bangalore : Electrical Engineer     -  
 select query example in php     -  
 SQL ORDER BY     -  
 Select odd rows from table     -  
 SQL COUNT Command     -  
 Change the content of select tag using JQuery     -  
 count elements in a vector     -  
 Unique Row Count Across Columns     -