Help with SQL query please

Question

Help with SQL query please

sjerromeharris

SSC-Addicted

Points: 434
More actions
July 10, 2012 at 5:29 am

#143647

I've been asked to provide a list of clients, ordered by the total figure billed and the branch the client is assigned to, for bills submitted since 1st July 2011. Bill details (client id (entityref), amounts and date of bill) are stored in the ac_billbook table. The Entities table stores the client ID and branch, and the Branches table records the ID and full name of the branch. Clients may have been sent more than one bill in the last year so I've included the Sum function.
My query is below. But its not displaying as I would expect - I'd like it to be ordered by department, then by sum(ac_billbook.costsnet), but there doesn't seem to be any order to the results.
SELECT Branches.Description, Sum(Ac_Billbook.CostsNet) AS SumOfCostsNet, Ac_Billbook.EntityRef
FROM (Entities INNER JOIN Ac_Billbook ON Entities.Code = Ac_Billbook.EntityRef) INNER JOIN Branches ON Entities.BranchRef = Branches.Code
GROUP BY Branches.Description, Ac_billbook.costsnet, Ac_Billbook.EntityRef
HAVING min(Ac_Billbook.billdate) >= '2011-06-30'
Thanks in advance
Sarah

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

without an explicit ORDER BY department,SumOfCostsNet at the end of your query, SQL server is free to return the data in any order at all.

the order you get is the order that SQL determined it could get the data the fastest, so you'll want to always include an ORDER BY statement where order is important.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sjerromeharris SSC-Addicted Points: 434 More actions · Answer 2

Thanks - v helpful.

If I want to select just the top 10 clients in terms of costs billed/branch (top ten clients in branch 1, top ten in branch 2, etc), do I just alter the SELECT element of the query along the lines of:

SELECT Branches.Description, TOP 10 Sum(Ac_Billbook.CostsNet) AS SumOfCostsNet, Ac_Billbook.EntityRef

Or have I massively over-simplified that?!

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 3

sjerromeharris (7/10/2012)
Thanks - v helpful.
If I want to select just the top 10 clients in terms of costs billed/branch (top ten clients in branch 1, top ten in branch 2, etc), do I just alter the SELECT element of the query along the lines of:
SELECT Branches.Description, TOP 10 Sum(Ac_Billbook.CostsNet) AS SumOfCostsNet, Ac_Billbook.EntityRef
Or have I massively over-simplified that?!
Thanks

great question; it goes right back to the order by again, though; TOP 10 will not give you what you are expecting without that ORDER BY command.

without the ORDER BY, top 10 gets kind of meaningless...it's that order-by-fastest-access vs order by SumOfCostsNet;

so if the objective is the top 10 with the highest (or lowest) totals in SumOfCostsNet, it's ORDER BY SumOfCostsNet.

if it's the top 10 alphabetically, it's ORDER BY Branches.Description

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sjerromeharris SSC-Addicted Points: 434 More actions · Answer 4

I've added in the top 10 but the query still needs a bit of tweaking

The query below lists the top 10 for the first branch, whereas I want it to list the top 10 for each different branch (there are 13 branches I think). Also, I'm getting the first 10 rows I think which all happen to be a negative figure (I think we must have refunded the client), so how can I show the highest value rather than the first/top 10 rows.

SELECT TOP 10 Sum(Ac_Billbook.CostsNet) AS SumOfCostsNet, Branches.Description, Ac_Billbook.EntityRef

FROM (Entities INNER JOIN Ac_Billbook ON Entities.Code = Ac_Billbook.EntityRef) INNER JOIN Branches ON Entities.BranchRef = Branches.Code

GROUP BY Branches.Description, Ac_Billbook.CostsNet, Ac_Billbook.EntityRef

HAVING min(Ac_Billbook.billdate) >= '2011-06-30'

ORDER BY branches.description, Ac_Billbook.CostsNet, Ac_Billbook.EntityRef

Any advice gratefully received.

(You'll really have to spell it out to me using small words, because I'm getting a bit frazzled and there seems to be a direct correlation between stress and ability to comprehend!!)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

-- with CostsNet in the GROUP BY, there's no aggregate of CostsNet

-- Try the original query with and without SUM

-- Then take CostsNet out of the GROUP BY and check

-- that the results are what you'd expect (ignore TOP for now);

SELECT --TOP 10

b.[Description],

a.EntityRef,

Sum(a.CostsNet) AS SumOfCostsNet

FROM Entities e

INNER JOIN Ac_Billbook a ON e.Code = a.EntityRef

INNER JOIN Branches b ON e.BranchRef = b.Code

GROUP BY b.[Description], a.EntityRef --, a.CostsNet

HAVING MIN(a.billdate) >= '2011-06-30'

ORDER BY b.[description], a.EntityRef--, a.CostsNet

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden