can this query be cleaner or written better?

7 Jan 2006 12:18 AM

Rich

Hello,

Below are 2 tables and the data (fictitious). I have a pretend flower shop.
I sell various kinds of flowers for 5 days (mon, tue...fri). First I want
to get a grouping of flowers sold per day, then I want to identify/isolate
the day with the highest count of flowers sold - should be Wednesday with a
count of 59 flowers sold. My query below seems a little on the kludgy side.
Any suggestions for improvement would be greatly appreciated.

create table msTbl(
ID int,
daysold varchar(10))

create table dtTbl(
ID int,
flower varchar(10),
quantity int)

insert into msTbl(ID, daysold)
select 1, 'monday' union
select 2, 'tuesday' union
select 3, 'wednesday' union
select 4, 'thursday' union
select 5, 'friday'

insert into dtTbl(ID, flower, quantity)
select 1, 'roses', 15 union
select 1, 'dafodils', 7 union
select 1, 'orchids', 8 union
select 2, 'roses', 19 union
select 2, 'ferns', 22 union
select 2, 'lilies', 14 union
select 3, 'orchids', 27 union
select 3, 'roses', 19 union
select 3, 'carnations', 13 union
select 4, 'dafodils', 11 union
select 4, 'petunias', 15 union
select 4, 'carnations', 22 union
select 5, 'roses', 12 union
select 5, 'orchids', 9 union
select 5, 'dafodils', 16 union
select 5, 'petunias', 18

--kludgy query - can this be improved - not so much shortened - I mean I
know I could alias msTbl and dtTbl - but a better Tsql technique?

SELECT t2.daySold, t2.SumOfQuantity
FROM (SELECT msTbl.daySold, Sum(dtTbl.Quantity) AS SumOfQuantity
FROM msTbl INNER JOIN dtTbl ON msTbl.ID = dtTbl.ID
GROUP BY msTbl.daySold) t2
WHERE t2.SumOfQuantity In (SELECT Max(sumofquantity) AS maxQuant
FROM (SELECT msTbl.daySold, Sum(dtTbl.Quantity) AS SumOfQuantity
FROM msTbl INNER JOIN dtTbl ON msTbl.ID = dtTbl.ID
GROUP BY msTbl.daySold) AS t1)

--output

wednesday 59

Thanks,
Rich

7 Jan 2006 12:34 AM

Hugo Kornelis

On Fri, 6 Jan 2006 16:18:02 -0800, Rich wrote:

>Hello,
>
>Below are 2 tables and the data (fictitious). I have a pretend flower shop.
> I sell various kinds of flowers for 5 days (mon, tue...fri). First I want
>to get a grouping of flowers sold per day, then I want to identify/isolate
>the day with the highest count of flowers sold - should be Wednesday with a
>count of 59 flowers sold. My query below seems a little on the kludgy side.
> Any suggestions for improvement would be greatly appreciated.
(snip)

Hi Rich,

Thanks for posting CREATE TABLE and INSERT statements!!

This query will also return the required results:

SELECT TOP 1 msTbl.daySold, SUM(dtTbl.Quantity) AS SumOfQuantity
FROM         msTbl
INNER JOIN   dtTbl
      ON     dtTbl.ID = msTbl.ID
GROUP BY     msTbl.daySold
ORDER BY     SumOfQuantity DESC

--
Hugo Kornelis, SQL Server MVP

7 Jan 2006 12:41 AM

Jens

SELECT flower, daysold,SUM(Quantity) SumSold
FROM dtTbl dt
INNER JOIN msTbl ms
ON ms.ID = dt.ID
GROUP BY daysold,flower
order by daysold,flower

SELECT TOP 1 daysold,SoldFlowers
FROM
(
    SELECT daysold,SUM(Quantity) SoldFlowers
    FROM dtTbl dt
    INNER JOIN msTbl ms
    ON ms.ID = dt.ID
    GROUP BY daysold
) SubQuery
GROUP BY daysold,soldflowers
ORDER BY SoldFlowers DESC

HTH, Jens Suessmeyer.

7 Jan 2006 12:43 AM

Jens

SELECT flower, daysold,SUM(Quantity) SumSold
FROM dtTbl dt
INNER JOIN msTbl ms
ON ms.ID = dt.ID
GROUP BY daysold,flower
order by daysold,flower

SELECT TOP 1 daysold,SoldFlowers
FROM
(
    SELECT daysold,SUM(Quantity) SoldFlowers
    FROM dtTbl dt
    INNER JOIN msTbl ms
    ON ms.ID = dt.ID
    GROUP BY daysold
) SubQuery
GROUP BY daysold,soldflowers
ORDER BY SoldFlowers DESC

HTH, Jens Suessmeyer.

7 Jan 2006 1:17 AM

Rich

Thank you all for your replies. So the trick was in using the Top operator.
Much cleaner than my approach.

Thanks again all,

Rich

Show quote

"Rich" wrote:

> Hello,
>
> Below are 2 tables and the data (fictitious). I have a pretend flower shop.
> I sell various kinds of flowers for 5 days (mon, tue...fri). First I want
> to get a grouping of flowers sold per day, then I want to identify/isolate
> the day with the highest count of flowers sold - should be Wednesday with a
> count of 59 flowers sold. My query below seems a little on the kludgy side.
> Any suggestions for improvement would be greatly appreciated.
>
> create table msTbl(
> ID int,
> daysold varchar(10))
>
> create table dtTbl(
> ID int,
> flower varchar(10),
> quantity int)
>
> insert into msTbl(ID, daysold)
> select 1, 'monday' union
> select 2, 'tuesday' union
> select 3, 'wednesday' union
> select 4, 'thursday' union
> select 5, 'friday'
>
> insert into dtTbl(ID, flower, quantity)
> select 1, 'roses', 15 union
> select 1, 'dafodils', 7 union
> select 1, 'orchids', 8 union
> select 2, 'roses', 19 union
> select 2, 'ferns', 22 union
> select 2, 'lilies', 14 union
> select 3, 'orchids', 27 union
> select 3, 'roses', 19 union
> select 3, 'carnations', 13 union
> select 4, 'dafodils', 11 union
> select 4, 'petunias', 15 union
> select 4, 'carnations', 22 union
> select 5, 'roses', 12 union
> select 5, 'orchids', 9 union
> select 5, 'dafodils', 16 union
> select 5, 'petunias', 18
>
> --kludgy query - can this be improved - not so much shortened - I mean I
> know I could alias msTbl and dtTbl - but a better Tsql technique?
>
> SELECT t2.daySold, t2.SumOfQuantity
> FROM (SELECT msTbl.daySold, Sum(dtTbl.Quantity) AS SumOfQuantity
> FROM msTbl INNER JOIN dtTbl ON msTbl.ID = dtTbl.ID
> GROUP BY msTbl.daySold) t2
> WHERE t2.SumOfQuantity In (SELECT Max(sumofquantity) AS maxQuant
> FROM (SELECT msTbl.daySold, Sum(dtTbl.Quantity) AS SumOfQuantity
> FROM msTbl INNER JOIN dtTbl ON msTbl.ID = dtTbl.ID
> GROUP BY msTbl.daySold) AS t1)
>
> --output
>
> wednesday 59
>
> Thanks,
> Rich
>

8 Jan 2006 12:59 PM

Gert-Jan Strik

If you don't want to use TOP + ORDER BY or SET ROWCOUNT + ORDER BY, then
you could write the query like this:

SELECT daysold,SUM(Quantity) SoldFlowers
FROM dtTbl dt
INNER JOIN msTbl ms
ON ms.ID = dt.ID
GROUP BY daysold
HAVING SUM(Quantity) >= ALL (
SELECT SUM(Quantity)
FROM dtTbl dt
INNER JOIN msTbl ms
ON ms.ID = dt.ID
GROUP BY daysold
)

Note that this query would return two rows if there also happened to be
another day that sold 59 flowers.

HTH,
Gert-Jan

Rich wrote:
Show quote

>
> Thank you all for your replies. So the trick was in using the Top operator.
> Much cleaner than my approach.
>
> Thanks again all,
>
> Rich
>
> "Rich" wrote:
>
> > Hello,
> >
> > Below are 2 tables and the data (fictitious). I have a pretend flower shop.
> > I sell various kinds of flowers for 5 days (mon, tue...fri). First I want
> > to get a grouping of flowers sold per day, then I want to identify/isolate
> > the day with the highest count of flowers sold - should be Wednesday with a
> > count of 59 flowers sold. My query below seems a little on the kludgy side.
> > Any suggestions for improvement would be greatly appreciated.
> >
> > create table msTbl(
> > ID int,
> > daysold varchar(10))
> >
> > create table dtTbl(
> > ID int,
> > flower varchar(10),
> > quantity int)
> >
> > insert into msTbl(ID, daysold)
> > select 1, 'monday' union
> > select 2, 'tuesday' union
> > select 3, 'wednesday' union
> > select 4, 'thursday' union
> > select 5, 'friday'
> >
> > insert into dtTbl(ID, flower, quantity)
> > select 1, 'roses', 15 union
> > select 1, 'dafodils', 7 union
> > select 1, 'orchids', 8 union
> > select 2, 'roses', 19 union
> > select 2, 'ferns', 22 union
> > select 2, 'lilies', 14 union
> > select 3, 'orchids', 27 union
> > select 3, 'roses', 19 union
> > select 3, 'carnations', 13 union
> > select 4, 'dafodils', 11 union
> > select 4, 'petunias', 15 union
> > select 4, 'carnations', 22 union
> > select 5, 'roses', 12 union
> > select 5, 'orchids', 9 union
> > select 5, 'dafodils', 16 union
> > select 5, 'petunias', 18
> >
> > --kludgy query - can this be improved - not so much shortened - I mean I
> > know I could alias msTbl and dtTbl - but a better Tsql technique?
> >
> > SELECT t2.daySold, t2.SumOfQuantity
> > FROM (SELECT msTbl.daySold, Sum(dtTbl.Quantity) AS SumOfQuantity
> > FROM msTbl INNER JOIN dtTbl ON msTbl.ID = dtTbl.ID
> > GROUP BY msTbl.daySold) t2
> > WHERE t2.SumOfQuantity In (SELECT Max(sumofquantity) AS maxQuant
> > FROM (SELECT msTbl.daySold, Sum(dtTbl.Quantity) AS SumOfQuantity
> > FROM msTbl INNER JOIN dtTbl ON msTbl.ID = dtTbl.ID
> > GROUP BY msTbl.daySold) AS t1)
> >
> > --output
> >
> > wednesday 59
> >
> > Thanks,
> > Rich
> >