Newbie working through a query!

Other SQL Server Topics
1

Hello again. I'm a newbie who is learning SQL via SQL Server Express. I've gotten a nearby expert to give me some test questions based on Adventureworks, and I get so far before I hit a wall and go looking for clues. Here's today's edition...

The question is, "List the total sales dollar amount for all invoices with more than 4 distinct items."

With that in mind, I cam up with this little bit of stuff that SEEMS to at least give me everything BUT the total amount, the sum of the Line Total column...

SELECT SalesOrderID, COUNT () AS Num_Of_Items, SUM (LineTotal) As 'Amount'
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING COUNT() > 4;

Getting the sum of that right column seems like it oughta be easy enough, but I can't figure out how to do it! I've probably gone about this all wrong, but I'm trying. With repetition comes familiarity and all that, so I appreciate the assist and patience from you fine experienced folk!

2

Looks OK to me. What is apparently wrong with the results you're getting?

3

I need to get the sum of the line totals. As it is I just have a column
with the line totals for each order.

4

may just be me, but i'm not understanding the subtlety of that.

Post an example of the desired output pls?

5

As it is, my results give me a list of all invoices with more than four distinct items, and each row has the 'line total' column with the dollar amount for that order. I need to get the sum of all those order totals in one figure. Does that make sense?

6

Hi,

I think I know what you mean and want, I think you need to employ some partitioning using the PARTITION BY, I have to admit to not being an expert on this one but someone on here may think "hey, that's an idea" and assist further.

To get you going have a read of https://msdn.microsoft.com/en-us/library/ms189461.aspx and see if that may point you in the right direction...

7

Yes, but I'd prefer an example otherwise my time figuring it out might be answering the wrong question! If another answer gets you the right solution then that's fine too :slight_smile: