"Peter" <peter@local.local> wrote in message
news:%23sSSwmehGHA.896@TK2MSFTNGP02.phx.gbl...
> Dear group,
> how do I get all authors together with their sales.qty
> something like this:
> use pubs
> go
> select au_lname, qty from authors left join titleauthor on authors.au_id =
> titleauthor.au_id, sales  where sales.title_id = titleauthor.title_id
> go
> I'd like the query to return only 23 rows from authors
> How can I do it?
> TIA
> Peter
>

"Rob Farley" <rob_far***@hotmail.com> wrote in message
news:OOu9HsehGHA.1508@TK2MSFTNGP04.phx.gbl...
> 'group by' is what you want:
>
> select au_lname, sum(qty) from authors left join titleauthor on
> authors.au_id =
> titleauthor.au_id, sales  where sales.title_id = titleauthor.title_id
> group by au_lname
>
> "Peter" <peter@local.local> wrote in message
> news:%23sSSwmehGHA.896@TK2MSFTNGP02.phx.gbl...
>> Dear group,
>> how do I get all authors together with their sales.qty
>> something like this:
>> use pubs
>> go
>> select au_lname, qty from authors left join titleauthor on authors.au_id
>> = titleauthor.au_id, sales  where sales.title_id = titleauthor.title_id
>> go
>> I'd like the query to return only 23 rows from authors
>> How can I do it?
>> TIA
>> Peter
>>
>
>

"Tom Cooper" <tom.no.spam.please.cooper@comcast.net> wrote in message
news:fLWdnQnc0eL4IeLZnZ2dnUVZ_vydnZ2d@comcast.com...
> But be careful what column(s) you use to group by.  Since more than one
> author can have the same last name, grouping by au_lname can combine
> multiple authors into one row in the result set.
>
> Also, I would recommend as good practice that you do not use the "old" SQL
> Server join syntax for the join to the sales table.  Both of your joins
> should be left joins if you want to see all 23 authors including the ones
> which have no titles or no sales.  Since this will get you a NULL value in
> qty for those authors, you should use the Coalesce or IsNull functions to
> change the nulls to 0.
>
> So your query might look something like (I have added au_id to the select
> list so you can see which author has which sales when there are duplicate
> last names).
>
> select authors.au_id, max(au_lname) as 'au_lname', sum(coalesce(qty,0)) as
> 'qty'
> from authors
> left join titleauthor on authors.au_id = titleauthor.au_id
> left join sales on sales.title_id = titleauthor.title_id
> group by authors.au_id
> order by max(au_lname)
>
> Tom
>
> "Rob Farley" <rob_far***@hotmail.com> wrote in message
> news:OOu9HsehGHA.1508@TK2MSFTNGP04.phx.gbl...
>> 'group by' is what you want:
>>
>> select au_lname, sum(qty) from authors left join titleauthor on
>> authors.au_id =
>> titleauthor.au_id, sales  where sales.title_id = titleauthor.title_id
>> group by au_lname
>>
>> "Peter" <peter@local.local> wrote in message
>> news:%23sSSwmehGHA.896@TK2MSFTNGP02.phx.gbl...
>>> Dear group,
>>> how do I get all authors together with their sales.qty
>>> something like this:
>>> use pubs
>>> go
>>> select au_lname, qty from authors left join titleauthor on authors.au_id
>>> = titleauthor.au_id, sales  where sales.title_id = titleauthor.title_id
>>> go
>>> I'd like the query to return only 23 rows from authors
>>> How can I do it?
>>> TIA
>>> Peter
>>>
>>
>>
>
>

"Tom Cooper" <tom.no.spam.please.cooper@comcast.net> wrote in message
news:fLWdnQnc0eL4IeLZnZ2dnUVZ_vydnZ2d@comcast.com...
> But be careful what column(s) you use to group by.  Since more than one
> author can have the same last name, grouping by au_lname can combine
> multiple authors into one row in the result set.
>
> Also, I would recommend as good practice that you do not use the "old" SQL
> Server join syntax for the join to the sales table.  Both of your joins
> should be left joins if you want to see all 23 authors including the ones
> which have no titles or no sales.  Since this will get you a NULL value in
> qty for those authors, you should use the Coalesce or IsNull functions to
> change the nulls to 0.
>
> So your query might look something like (I have added au_id to the select
> list so you can see which author has which sales when there are duplicate
> last names).
>
> select authors.au_id, max(au_lname) as 'au_lname', sum(coalesce(qty,0)) as
> 'qty'
> from authors
> left join titleauthor on authors.au_id = titleauthor.au_id
> left join sales on sales.title_id = titleauthor.title_id
> group by authors.au_id
> order by max(au_lname)
>
> Tom
>
> "Rob Farley" <rob_far***@hotmail.com> wrote in message
> news:OOu9HsehGHA.1508@TK2MSFTNGP04.phx.gbl...
>> 'group by' is what you want:
>>
>> select au_lname, sum(qty) from authors left join titleauthor on
>> authors.au_id =
>> titleauthor.au_id, sales  where sales.title_id = titleauthor.title_id
>> group by au_lname
>>
>> "Peter" <peter@local.local> wrote in message
>> news:%23sSSwmehGHA.896@TK2MSFTNGP02.phx.gbl...
>>> Dear group,
>>> how do I get all authors together with their sales.qty
>>> something like this:
>>> use pubs
>>> go
>>> select au_lname, qty from authors left join titleauthor on authors.au_id
>>> = titleauthor.au_id, sales  where sales.title_id = titleauthor.title_id
>>> go
>>> I'd like the query to return only 23 rows from authors
>>> How can I do it?
>>> TIA
>>> Peter
>>>
>>
>>
>
>