November 16, 2007 at 5:26 am
hi,
group by clause work fine with having clause but
can be use group by clause with where clause ,if yes how ?
thanks
November 16, 2007 at 5:32 am
Of course!
You have to put the 'group by' clause after the where condition.
Here's one example:
Select t, count(*) from yourtable
where t in ('A', 'B')
group by t
November 17, 2007 at 5:26 am
Hi,
HAVING was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM), and without HAVING
it would be impossible to test for result conditions.
The syntax for the HAVING function is:
SELECT column,SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value
In other hand you can do a filter a the other column
SELECT column,SUM(column) FROM table
Where column>3000
GROUP BY column
HAVING SUM(column) condition value
This is a snapshot for Microsoft books Online
The HAVING clause is typically used in conjunction with the GROUP BY clause, although it can be specified without GROUP BY.
The HAVING clause specifies further filters that are applied after the WHERE clause filters.
For example, this WHERE clause only qualifies orders selling a product with a unit price exceeding $100,
and the HAVING clause further restricts the result to only thos orders that include more than 100 units:
SELECT OrdD1.OrderID AS OrderID,
SUM(OrdD1.Quantity) AS "Units Sold",
SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue
FROM [Order Details] AS OrdD1
WHERE OrdD1.OrderID in (SELECT DISTINCT OrdD2.OrderID
FROM [Order Details] AS OrdD2
WHERE OrdD2.UnitPrice > $100)
GROUP BY OrdD1.OrderID
HAVING SUM(OrdD1.Quantity) > 100
Regards
Ahmed
November 17, 2007 at 10:42 am
Lot of Thanks to Fracn and Ahmed to give me very useful information.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply