SQL Statement HELP.Select within a select I THINK>

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Statement HELP.Select within a select I THINK>

Author

Topic

krisr
Starting Member

10 Posts

Posted - 2006-05-08 : 10:08:38

Please can anyone help me with this statement.

I have to develop SQL Query that will get the top 50 Category Names under which products fall under,and order them by the sum of the amount of sales and display the sales amount.

Furthermore, I need to get the top 10 Products of each category also order them by the sum of the amount of sales and display the sales amount.

Now the first part I have done,I'm having trouble with the second part...Getting top 10 products per category.

This is my code so far...

SELECT TOP 50(StockCat_Category_Name)AS SCName,SUM(PRICE) AS TOTAL_SALES
FROM FACT_Transaction FT, DIM_Product DP, DIM_Client DC, DIM_Time DT
WHERE FT.Product_KEY = DP.Product_KEY
AND DT.Time_KEY = FT.Time_KEY
AND DC.Client_KEY = FT.Client_KEY
AND DT.[Month] = 4
AND DT.[Year] = 2006
AND DC.Client_SerialNo = '00001232'
GROUP BY StockCat_Category_Name
ORDER BY TOTAL_SALES DESC

Please can anyone help me. It's driving me up the wall!!!

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-09 : 01:51:56

Refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

krisr
Starting Member

10 Posts

Posted - 2006-05-09 : 02:16:58

Madhivanan, thanks for replying.

One thing though, the UNION ALL statement only works with data of the same kind. Here i have VARCHAR and INTEGERS,thus it displays errors. Any other ideas??

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-09 : 02:20:05

You need to convert integers as varchar

Use cast(intcol as varchar(10))

Madhivanan

Failing to plan is Planning to fail

krisr
Starting Member

10 Posts

Posted - 2006-05-09 : 02:48:43

Madhivanan, im really not getting this right.Please help.
Where do i put the CAST???

SELECT TOP 50(StockCat_Category_Name),SUM(PRICE) AS TOTAL_SALES
UNION ALL
SELECT TOP 10[Description],SUM(PRICE)
FROM FACT_Transaction FT, DIM_Product DP, DIM_Client DC, DIM_Time DT
WHERE FT.Product_KEY = DP.Product_KEY
AND DT.Time_KEY = FT.Time_KEY
AND DC.Client_KEY = FT.Client_KEY
AND DT.[Month] = 4
AND DT.[Year] = 2006
AND DC.Client_SerialNo = '86634'
GROUP BY StockCat_Category_Name
ORDER BY TOTAL_SALES DESC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-09 : 02:59:05

You will need to specify the full query.
It is


query1
union all
query2


SELECT TOP 50(StockCat_Category_Name),SUM(PRICE) AS TOTAL_SALES
FROM  ...
WHERE ...
GROUP BY ...
ORDER BY ...

UNION ALL

SELECT TOP 10[Description],SUM(PRICE)
FROM FACT_Transaction FT, DIM_Product DP, DIM_Client DC, DIM_Time DT
WHERE FT.Product_KEY = DP.Product_KEY
AND DT.Time_KEY = FT.Time_KEY
AND DC.Client_KEY = FT.Client_KEY
AND DT.[Month] = 4
AND DT.[Year] = 2006
AND DC.Client_SerialNo = '86634'
GROUP BY StockCat_Category_Name
ORDER BY TOTAL_SALES DESC

krisr
Starting Member

10 Posts

Posted - 2006-05-09 : 03:25:52

Madhivanan,did exactly what u said
Still giving stupid error messages.

I actually at first tried it this way,and thought it might be wrong until u replied to it. This is what i have done.

SELECT TOP 50(StockCat_Category_Name),SUM(PRICE) AS TOTAL_SALES
FROM FACT_Transaction FT, DIM_Product DP, DIM_Client DC, DIM_Time DT
WHERE FT.Product_KEY = DP.Product_KEY
AND DT.Time_KEY = FT.Time_KEY
AND DC.Client_KEY = FT.Client_KEY
AND DT.[Month] = 4
AND DT.[Year] = 2006
AND DC.Client_SerialNo = '86634'
GROUP BY StockCat_Category_Name
ORDER BY TOTAL_SALES DESC

UNION ALL

SELECT TOP 10[Description],SUM(PRICE)
FROM FACT_Transaction FT, DIM_Product DP, DIM_Client DC, DIM_Time DT
WHERE FT.Product_KEY = DP.Product_KEY
AND DT.Time_KEY = FT.Time_KEY
AND DC.Client_KEY = FT.Client_KEY
AND DT.[Month] = 4
AND DT.[Year] = 2006
AND DC.Client_SerialNo = '86634'
GROUP BY StockCat_Category_Name
ORDER BY TOTAL_SALES DESC

And this is the error messages i get.

Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'UNION'.

Am i just being really stupid or is it something big?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-09 : 03:31:36

sorry.. silly me...


select *
from
(
    SELECT TOP 50(StockCat_Category_Name),SUM(PRICE) AS TOTAL_SALES
    FROM FACT_Transaction FT, DIM_Product DP, DIM_Client DC, DIM_Time DT
    WHERE FT.Product_KEY = DP.Product_KEY
    AND DT.Time_KEY = FT.Time_KEY
    AND DC.Client_KEY = FT.Client_KEY
    AND DT.[Month] = 4
    AND DT.[Year] = 2006
    AND DC.Client_SerialNo = '86634'
    GROUP BY StockCat_Category_Name
    ORDER BY TOTAL_SALES DESC
) a
UNION ALL
select *
from
(
    SELECT TOP 10[Description],SUM(PRICE)
    FROM FACT_Transaction FT, DIM_Product DP, DIM_Client DC, DIM_Time DT
    WHERE FT.Product_KEY = DP.Product_KEY
    AND DT.Time_KEY = FT.Time_KEY
    AND DC.Client_KEY = FT.Client_KEY
    AND DT.[Month] = 4
    AND DT.[Year] = 2006
    AND DC.Client_SerialNo = '86634'
    GROUP BY StockCat_Category_Name
    ORDER BY TOTAL_SALES DESC
) b

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-09 : 03:38:08

in union, order by is specified at the 2nd query
--edit
or last query

--------------------
keeping it simple...

krisr
Starting Member

10 Posts

Posted - 2006-05-09 : 05:32:37

No still having problems.

giving error message : Server: Msg 8120, Level 16, State 1, Line 1
Column 'DP.Description' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Tried to get it working but nothing

krisr
Starting Member

10 Posts

Posted - 2006-05-09 : 06:17:33

I think i might not be very detailed in thi question.Let me rephrase.

This is what i want.

STOCK_CAT_NAME TOTAL_SALES DESCRIPTION TOTAL SALES

Chocolates 18955.55 Bar One 7985.34
Caramel Bar 3485.05
Nut Bar 1445.43
Crunchie 503.44
..... .....
.... .....
.... .....
..... .....
..... .....
..... .....

Cooldrinks 14093.43 Coke 9443.00
Creme Soda 2845.00
Pepsi 445.00
..... .....
..... .....
..... .....
..... .....
..... .....
..... .....
..... .....

Ok this is the idea.
Where the .... is, this is data that should be there once the query is completed. Just using an example of chocolates and drinks

So stock_cat_name should appear 50 times ie. The 50 top Categories according to sales.
And Description should appear 10 times for each Stock_cat_name ie. The 10 top Descriptions of the product ordered according to sales .

Is that possible or am i digging to deep now??
Please help.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-09 : 06:25:21

sorry I did not look carefully in your codes.

select *
from
(
    SELECT TOP 50(StockCat_Category_Name),SUM(PRICE) AS TOTAL_SALES
    FROM FACT_Transaction FT, DIM_Product DP, DIM_Client DC, DIM_Time DT
    WHERE FT.Product_KEY = DP.Product_KEY
    AND DT.Time_KEY = FT.Time_KEY
    AND DC.Client_KEY = FT.Client_KEY
    AND DT.[Month] = 4
    AND DT.[Year] = 2006
    AND DC.Client_SerialNo = '86634'
    GROUP BY StockCat_Category_Name
    ORDER BY TOTAL_SALES DESC
) a
UNION ALL
select *
from
(
    SELECT TOP 10[Description],SUM(PRICE)
    FROM FACT_Transaction FT, DIM_Product DP, DIM_Client DC, DIM_Time DT
    WHERE FT.Product_KEY = DP.Product_KEY
    AND DT.Time_KEY = FT.Time_KEY
    AND DC.Client_KEY = FT.Client_KEY
    AND DT.[Month] = 4
    AND DT.[Year] = 2006
    AND DC.Client_SerialNo = '86634'
    GROUP BY StockCat_Category_Name [Description]
    ORDER BY TOTAL_SALES DESC
) b

krisr
Starting Member

10 Posts

Posted - 2006-05-09 : 06:32:05

The previous message did not really look nice let me try again...

I think i might not be very detailed in thi question.Let me rephrase.

This is what i want.

STOCK_CAT_NAME TOTAL_SALES DESCRIPTION TOTAL SALES
Chocolates 18955.55 Bar One 7985.34
Caramel Bar 3485.05
Nut Bar 1445.43
Crunchie 503.44
..... .....
..... .....
..... .....
..... .....
..... .....
..... .....
Cooldrinks 14093.43 Coke 9443.00
Creme Soda 2845.00
Pepsi 445.00
..... .....
..... .....
..... .....
..... .....
..... .....
..... .....
..... .....

Ok this is the idea.
Where the .... is, this is data that should be there once the query is completed. Just using an example of chocolates and drinks

So stock_cat_name should appear 50 times ie. The 50 top Categories according to sales.
And Description should appear 10 times for each Stock_cat_name ie. The 10 top Descriptions of the product ordered according to sales .

Is that possible or am i digging to deep now??
Please help.

Thanks.

krisr
Starting Member

10 Posts

Posted - 2006-05-09 : 06:39:42

Please my i have your email address.Cant seem to get the format right.Ill email you the format

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-09 : 06:43:41

Have you try the codes that i just posted ?

krisr
Starting Member

10 Posts

Posted - 2006-05-09 : 06:54:06

Yes i have tried it. But its not what i exactly want.Cant really see if the data is right. It is currently just showing me record 1 - 50 is the top stock_cat_name and then next to it the total sales for that category.

It displays record 51 - 60 with data.

The thing is I want record 1 to show the StockCatName, the total sales for that category, then next to it the top 10 products for that category according to sales and next to that the total sales amount of that product.

so in total i want for things displayed....

StockCatName, Total_SALES_for_StockCatName, Product_description, Total_Sales_for_Product

There should then be 50 StockCatNames...As I want the top 50
But every StockCatName should display it's top 10 products per sales amount...

Is that a little bit more clear?
I can email you an example of my format...If that will help.

thanks

krisr
Starting Member

10 Posts

Posted - 2006-05-10 : 04:34:06

Anyone gonnahelp me on this???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources