Can't get my head around this...

For the following query I am trying to visualize how the where clause causes
the rankings to occur.
WHERE s.totalsales <= t.totalsales
can someone please explain this?
SELECT s.state, st.stor_name,s.totalsales,Rank=COUNT(*)
FROM (SELECT t.state, t.stor_id, SUM(s.qty) AS TotalSales
FROM sales s JOIN stores t ON (s.stor_id=t.stor_id)
GROUP BY t.state, t.stor_id) s JOIN
(SELECT t.state, t.stor_id, SUM(s.qty) AS TotalSales
FROM sales s JOIN stores t ON (s.stor_id=t.stor_id)
GROUP BY t.state, t.stor_id) t ON (s.state=t.state)
JOIN stores st ON (s.stor_id=st.stor_id)
WHERE s.totalsales <= t.totalsales
GROUP BY s.state,st.stor_name,s.totalsales
ORDER BY s.state, rank"cmatero" <cmatero@.discussions.microsoft.com> wrote in message
news:39A588F4-E56E-497A-B507-957AEBE1F09B@.microsoft.com...
> For the following query I am trying to visualize how the where clause
> causes
> the rankings to occur.
> WHERE s.totalsales <= t.totalsales
> can someone please explain this?
Sure, RANK is the count of the greater-than-or-equal-to entities.
Although the easiest way to visualize this is to unwrap the GROUP BY
and look at the un-grouped result.
SELECT s.state, st.stor_id,s.totalsales,t.stor_id higher_sales_store
FROM (SELECT t.state, t.stor_id, SUM(s.qty) AS TotalSales
FROM sales s JOIN stores t ON (s.stor_id=t.stor_id)
GROUP BY t.state, t.stor_id) s
JOIN (SELECT t.state, t.stor_id, SUM(s.qty) AS TotalSales
FROM sales s JOIN stores t ON (s.stor_id=t.stor_id)
GROUP BY t.state, t.stor_id) t ON (s.state=t.state)
JOIN stores st ON (s.stor_id=st.stor_id)
WHERE s.totalsales <= t.totalsales
ORDER BY s.state,st.stor_name,s.totalsales
If you have 3 stores in TX,
state stor_id totalsales
TX STOREA 500
TX STOREB 400
TX STOREC 300
This will look like
state stor_id totalsales higher_sales_store
TX STOREA 500 STOREA
TX STOREB 400 STOREA
TX STOREB 400 STOREB
TX STOREC 300 STOREA
TX STOREC 300 STOREB
TX STOREC 300 STOREC
When you group this by store_id
state stor_id totalsales count(*)=RANK
TX STOREA 500 1
TX STOREB 400 2
TX STOREC 300 3
David|||cmatero wrote:
> For the following query I am trying to visualize how the where clause caus
es
> the rankings to occur.
> WHERE s.totalsales <= t.totalsales
> can someone please explain this?
>
> SELECT s.state, st.stor_name,s.totalsales,Rank=COUNT(*)
> FROM (SELECT t.state, t.stor_id, SUM(s.qty) AS TotalSales
> FROM sales s JOIN stores t ON (s.stor_id=t.stor_id)
> GROUP BY t.state, t.stor_id) s JOIN
> (SELECT t.state, t.stor_id, SUM(s.qty) AS TotalSales
> FROM sales s JOIN stores t ON (s.stor_id=t.stor_id)
> GROUP BY t.state, t.stor_id) t ON (s.state=t.state)
> JOIN stores st ON (s.stor_id=st.stor_id)
> WHERE s.totalsales <= t.totalsales
> GROUP BY s.state,st.stor_name,s.totalsales
> ORDER BY s.state, rank
The column Rank (Rank = Count(*)) is sorted by the ORDER BY clause in
ascending order. Which means the state/stores w/ the lowest total sales
will be at the top of the list.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Your WHERE clause there appears to be performing two functions. The GROUP
BY clauses are ensuring that everything is grouped by State and Stor_ID:
1. It's ensuring that each record has a unique Rank value, since the Rank
is assigned the value of the Count(*) function. As the results are
iterated, s.TotalSales <= t.TotalSales puts a restriction on the results
that ensures Rank=1 for the first record encountered for a state, and Rank=2
for the second record encountered for that state. Without this restrictive
WHERE clause you'll end up with the same Rank (the total number of records
for each state) assigned to each store in a state. i.e., if you have three
records for NY every store will be ranked 3.
2. It ensures the assignment of Ranks by TotalSales numbers. The way it is
written, the store with the highest TotalSales for a state is ranked 1, the
next highest is ranked 2. If you switch this around to WHERE s.TotalSales
>= t.TotalSales, the results will be ranked in reverse - your store in each
state with the lowest TotalSales numbers will be ranked 1, the next highest
will be ranked 2, etc.
"cmatero" <cmatero@.discussions.microsoft.com> wrote in message
news:39A588F4-E56E-497A-B507-957AEBE1F09B@.microsoft.com...
> For the following query I am trying to visualize how the where clause
> causes
> the rankings to occur.
> WHERE s.totalsales <= t.totalsales
> can someone please explain this?
>
> SELECT s.state, st.stor_name,s.totalsales,Rank=COUNT(*)
> FROM (SELECT t.state, t.stor_id, SUM(s.qty) AS TotalSales
> FROM sales s JOIN stores t ON (s.stor_id=t.stor_id)
> GROUP BY t.state, t.stor_id) s JOIN
> (SELECT t.state, t.stor_id, SUM(s.qty) AS TotalSales
> FROM sales s JOIN stores t ON (s.stor_id=t.stor_id)
> GROUP BY t.state, t.stor_id) t ON (s.state=t.state)
> JOIN stores st ON (s.stor_id=st.stor_id)
> WHERE s.totalsales <= t.totalsales
> GROUP BY s.state,st.stor_name,s.totalsales
> ORDER BY s.state, rank