SQL Server Basic – VI

–Finding the Grouped SUM based on several filtering criteria

SELECT   STOR_ID [Store Id],SUM(QTY) [Total Quantity]
FROM     sales
WHERE   title_id like 'PC%'
AND       Stor_id between 7000 and 8000
OR          ord_date between '1994-01-01' and '1995-01-01'
GROUP BY STOR_ID

SUM acts just like Count, but instead of number of rows, accumulates the content of any numeric field like QTY here.

–Finding the Average based on several filtering criteria

select avg(discount) avg_discount from discounts
go
SELECT   PAYTERMS,AVG(QTY) [Average Quantity]
FROM     sales
where ord_date < '1994-01-01'
GROUP BY PAYTERMS

AVG is also a grouping function and would average out the quantity against the grouping parameter..

–Grouping By an expression containing a column

select distinct(Left(upper(name),1)) TABLE_INITIALS, count(*) OCCURENCES
from SysObjects
where xtype = 'U'
group by Left(upper(name),1)

Assigning priorities in filtering a query with the help of proper bracketting (parenthesis)

SELECT * from Pubs..sales
WHERE title_id like 'PC%'
AND (
Stor_id between 7000 and 8000
OR ord_date between '1994-01-01' and '1995-01-01'
)
-----
SELECT * from Pubs..sales
WHERE (
title_id like 'PC%'
and Stor_id between 7000 and 8000
)
OR ord_date between '1994-01-01' and '1995-01-01'

In the first query the bracketted WHERE clause gives 2 results and the AND clause gives 19 results. (select * from Pubs..sales where Stor_id between 7000 and 8000 OR ord_date between ‘1994-01-01’ and ‘1995-01-01’ gives 19 results). There intersection gives only 1 result as common.

–In the second query the bracketted WHERE clause gives 1 result
–and the OR clause gives 8 results.Since this is a cumulative OR clause,
–we would get 9 rows in total.

Advertisements

#sql-basic