Total members 11621 |It is currently Wed Mar 29, 2017 8:00 pm Login / Join Codemiles

Java

C/C++

PHP

C#

HTML

CSS

ASP

Javascript

JQuery

AJAX

XSD

Python

Matlab

R Scripts

Weka

All times are UTC [ DST ]



  SELECT-Group by and Order by,Having Clause,count(),Joins
Sat Apr 07, 2007 7: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
SELECT city
FROM employees
GROUP BY city
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;



3.Joins

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;



_________________
Recommend my post if you found it helpful.


Author:
Newbie
User avatar Posts: 15
Have thanks: 0 time
  Re: SELECT-Group by and Order by,Having Clause,count(),Joins
Thu May 09, 2013 11:56 pm 

updated.


Author:
Mastermind
User avatar Posts: 2704
Have thanks: 74 time
Post new topic Reply to topic  [ 2 posts ] 

  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     -  
 Unique Row Count Across Columns     -  
 String char count     -  

All times are UTC [ DST ]









Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
All copyrights reserved to codemiles.com 2007-2011
mileX v1.0 designed by codemiles team