min over a sum ?? but HOW?

[ice-9]

OK, i have 1 table which contains salesfacts. There are 2 things I want:
1. Show the revenue and store number.
2. Show only the store with the lowest revenue.

the first one is simple:

• Select store_key, sum(dollar_sales)
• from SALES_FACT
• group by store_key;

This shows the revenue per store, but now i want only the store with the lowest revenue. So it should be a min operator over the sum, how do i make that query???

its driving me nuts

iCe

 

[s1police]

You can use the query that you mentioned in your pervious post, I assume it is query1.

Query1 contain the below column

Store_Key Sum(dollar_sale)
========================
1 500
2 1,000
3 1,800
4 20
5 670

You can create another query, let's say query2, which is based on query1, and make the Min() function for that.

For example,

SELECT Store_Key, Min(Sum(Dollar_sale)) As Min_Dollar_Sale
FROM query1
GROUP BY Store_Key;

It should show the minimum Dollar_sale for you. Duplicate will appear when the same minimum amount are calculated.

Victor

 

[ice-9]

You can use the query that you mentioned in your pervious post, I assume it is query1.

Query1 contain the below column

Store_Key Sum(dollar_sale)
========================
1 500
2 1,000
3 1,800
4 20
5 670

You can create another query, let's say query2, which is based on query1, and make the Min() function for that.

For example,

SELECT Store_Key, Min(Sum(Dollar_sale)) As Min_Dollar_Sale
FROM query1
GROUP BY Store_Key;

It should show the minimum Dollar_sale for you. Duplicate will appear when the same minimum amount are calculated.

Victor

thank you for replying Victor. I tried this one, but when i select the store_key it will still give all the stores in the result. When i remove store_key it will show the correct amount, but with no store_key...

n2

 

[s1police]

Can you try not using the Store_Key? Let's say using the store name.

 

[DCrake]

Simple Software Solutions

SELECT TOP 1 Store_Key, Sum(Dollar_sale) As Turnover
FROM query1
GROUP BY Store_Key ORDER BY Sum(Dollar_Sale) As Turnover DESC;

So above we are still grouping by store and summing their sales, however we arre sorting the sales in descending order, smallest first, and stating we only want to show the first one (TOP 1)

CodeMaster:

 

[Brianwarnock]

You actually need a 3rd query joining the value in the 2nd, without the Storekey selected, back to the first to pull in the Store key.

Brian

Edit, Or as DCrake suggests

 

[s1police]

You can have a look into the MS Access. I try to use TOP 1 suggested by Dcrake~

I dont know why I cannot show up the min(Sum(Turnover)) with store_key when I create the new DB. However, I can show up the min(cost) when using my existing DB....

so strange....