top 3 customer but one of them is sum of multiple parameter

[pke8jt]

Question
When generating a top 3 customer result, Is it possible to include a sum value in it?

my current query:

Sum of Dollar | Customer

10 | A
8 | B
5 | C

desired query output

14 | B_Total
10 | A
5 | C

B_Total (14) is the sum of X (4), Y (3), Z (2) AND B (5)

And [History.CustomerNo] has Customer A, B, C, ... X, Y, Z.
Therefore, their common identifier is the table, [History] and parameter, [CustomerNo]

SQL:
SELECT TOP 3 Sum(History.DollarsSold) AS SumOfDollarsSold, History.CustomerNo
FROM dbo_SO_SalesHistory
WHERE (((sHistory.InvoiceDate) Between #8/1/2017# And #8/31/2017#))
GROUP BY History.CustomerNo
ORDER BY Sum(History.DollarsSold) DESC;

 

[isladogs]

The fact that you're asking suggests that you have either not tried your query or that it failed. Which?
If it failed what was the error?

Personally I would create the aggregate query and save it.
Then create a new query or SQL for the TOP 3 results of that.

 

[pke8jt]

Hi, Collin
Thank you for your response. I have tried inner join, format method, IIF statement but they don't seem like the solution that is applicable to my case.

So are you suggesting create a query of sum of f X (4), Y (3), Z (2) AND B (5), name it as B_Total

And a TOP 3 query or SQL with the B_Total?

 

[arnelgp]

Use Union Query:

SELECT Sum(DollarsSold) AS SumDollarsSold, "B_TOTAL" As CustomerNo
FROM yourTableName
WHERE (((yourTableName.[customerNo]) IN ("B","X","Y","Z")))
UNION
SELECT TOP 2
Sum(DollarsSold), CustomerNo FROM yourTableName
WHERE yourTableName.CustomerNo NOT IN ("B","X","Y","Z")
GROUP BY CustomerNo ORDER BY 1 DESC

 

[pke8jt]

Hi arnelgp, thank you so much for your reply.

I have syntax error in union query message.

Would you please give me any advice on this? Thank you so much again!

SQL:
SELECT Sum(DollarsSold) AS SumDollarsSold, "B_TOTAL" As CustomerNo
FROM dbo_SO_SalesHistory
WHERE ((dbo_SO_SalesHistory.[CustomerNo]) IN ("B","X","Y","Z"))
UNION
SELECT TOP 2
Sum(DollarsSold), CustomerNo FROM dbo_SO_SalesHistory
WHERE ((dbo_SO_SalesHistory.[CustomerNo]) NOT IN ("B","X","Y","Z"))
GROUP BY dbo_SO_SalesHistory.CustomerNo ORDER BY 1 DESC

 

[isladogs]

Hi, Collin
Thank you for your response. I have tried inner join, format method, IIF statement but they don't seem like the solution that is applicable to my case.

So are you suggesting create a query of sum of f X (4), Y (3), Z (2) AND B (5), name it as B_Total

And a TOP 3 query or SQL with the B_Total?

That's basically what I was suggesting.
The UNION query idea is another possibility but perhaps more complex to follow

 

[arnelgp]

Test each individual query to pinpoint which query is faulty.

 

[plog]

The individual statements of a UNION should not be complicated. They should simply contain SELECT and FROM clauses:

SELECT * FROM SubQueryA
UNION
SELECT * FROM SubQueryB

The complexity (WHERE, GROUP BY, HAVING) needs to go in the individual subqueries that comprise the UNION. Divide and conquer, it makes debugging so much easier.

 

[arnelgp]

There are only 2 fields involve, so its not that complicated.

 

[plog]

No, its pretty complicated. 1. He was unable to get it working correctly. 2. The SELECT clause is but one clause of an SQL statement. It's simple, but the others make it not.

 

[pke8jt]

Thank you for your reponses! Do you mind If I ask a follow-up question???

arnelgp code works except that I would like to
ORDER BY Sum(SalesHistory.DollarsSold) DESC;

Instead of ORDER BY 1 DESC; (It gives me the first 3 alphabetical parameters)

SQL:

SELECT Sum(SalesHistory.DollarsSold) AS SumDollarsSold, "B_Total" As CustomerNo
FROM SalesHistory
WHERE ((SalesHistory.[CustomerNo]) IN ("B","X","Y","Z"))
UNION
SELECT TOP 3
Sum(DollarsSold), CustomerNo FROM SalesHistory
WHERE ((SalesHistory.CustomerNo) NOT IN ("B","X","Y","Z"))
GROUP BY SalesHistory.CustomerNo ORDER BY 1 DESC;

 

[arnelgp]

Yes you can, actually 1 there refers to the first colum which is the sum.

 

[pke8jt]

Thank you for the reply, arnelgp!
When I run the code, I get

'The ORDER BY expression (Sum(SalesHistory.DollarsSold)) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression.'

SQL:
SELECT Sum(SalesHistory.DollarsSold) AS SumDollarsSold, "B_Total" As CustomerNo
FROM SalesHistory
WHERE ((SalesHistory.[CustomerNo]) IN ("B","X","Y","Z"))
UNION
SELECT TOP 3
Sum(DollarsSold), CustomerNo FROM SalesHistory
WHERE ((SalesHistory.CustomerNo) NOT IN ("B","X","Y","Z"))
GROUP BY SalesHistory.CustomerNo
ORDER BY (Sum(SalesHistory.DollarsSold)) DESC;

Thank you so much for being paitent with me and answering my follow-up questions.

 

[arnelgp]

If you remove the extra parenthesis, otherwise you'll have to use 1

 

[pke8jt]

Neither of them works for me.. But I really appreciate all your time!! Thank you so much!

 

[isladogs]

I see you've been busy with the UNION approach.

Just out of interest have you tried what I suggested as I think it will be easier

 

[pke8jt]

Hi Arnelgp, I'm terribly so sorry for keep bothering you.

I found out when I run two queries separately, the 2nd part of query is giving me the output that I desire (corret_2.jgp),
But when I run it together with Union, then the order is somehow mixed up (wrong.jpg).

Would you please take a look at the images that I attached?

 

[arnelgp]

I am not in my computer right now, so i cannot test this, try:

Select SumDollarsSold, CustomerNo From
(SELECT Sum(SalesHistory.DollarsSold) AS SumDollarsSold, "B_Total" As CustomerNo
FROM SalesHistory
WHERE ((SalesHistory.[CustomerNo]) IN ("B","X","Y","Z"))
UNION All
SELECT TOP 3
Sum(DollarsSold), CustomerNo FROM SalesHistory
WHERE ((SalesHistory.CustomerNo) NOT IN ("B","X","Y","Z"))
GROUP BY SalesHistory.CustomerNo) ORDER BY SumDollarsSold DESC;