Skip to Content
0

Product Category Query

Dec 17, 2017 at 11:27 PM

64

avatar image
Hi Experts

We have categorised stock in A, B, C & D, Finished Stock, Lamps & Blanks and every month we run these queries to get the stock value of the different categories of stock

Have to run four queries to get these values, the first Query Product Category has to be run four times to get the results for the A, B, C & D

Then another query to find the results for Finished Goods

And then Another query for Lamps

And finally, another query to check for Blanks

I was wondering if its possible to combine all these queries to get the results as SubColumns, so When you run the query, it should display

Product Category A

Results

Sub Total

Product Category B

Results

Sub Total

Product Category C

Results

Sub Total

Product Category D

Results

Sub Total

Product Category – Finished Goods

Results

Sub Total

Product Category - Lamps

Results

Sub Total

Product Category - Blanks

Results

Sub Total



Please help me combine these queries

The Queries are below

1. Product Category

Product Category
SELECT
T0.[ItemCode] as 'Product Code', 
T0.[ItemName] as 'Product Description', 
T0.[AvgPrice], 
T0.[OnHand], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 
FROM OITM T0 
WHERE 
T0.[U_Category] =[%0]

2. Product Category - Finished Goods

Product Category – Finished Goods
SELECT 
T0.[ItemCode], 
T0.[ItemName], 
T0.[OnHand], 
T0.[AvgPrice], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 
FROM OITM T0 
WHERE T0.[ItmsGrpCod] <> '287' AND  
T0.[ItmsGrpCod] <> '288' AND  
T0.[OnHand] >= 1 
AND 
T0.[ItmsGrpCod] <> '289' 
ORDER BY T0.[ItemCode]

3. Product Category - Lamps

Product Category – Lamps
SELECT 
T0.[ItemCode], 
T0.[ItemName], 
T0.[OnHand], 
T0.[AvgPrice], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock'
FROM OITM T0 
WHERE T0.[ItmsGrpCod] <> '287' AND  
T0.[ItmsGrpCod] <> '288' AND  
T0.[OnHand] >= 1 AND 
T0.[ItmsGrpCod] = '289' 
ORDER BY T0.[ItemCode]

4. Product Category - Blanks

Product Category – Blanks
SELECT 
T0.[ItemCode] as 'Product Code', 
T0.[ItemName] as 'Product Description',  
T0.[OnHand], 
T0.[AvgPrice], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 
FROM OITM T0 
WHERE T0.[ItmsGrpCod] = '287' AND  
T0.[U_Category] IS NULL 
AND  T0.[OnHand] > 0

Thanks

Rahul

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Johan Hakkesteegt Dec 18, 2017 at 02:35 PM
1

Hi Rahul,

As long as the amount of columns is the same for each query, you can combine them using the UNION ALL function. The syntax is simple:

query 1 with 5 columns
UNION ALL
query 2 with 5 columns
UNION ALL
query 3 with 5 columns
etc.

You can add a text column at the beginning of each query to describe the Category. So you would get:

SELECT 'Category A',
T0.[ItemCode], 
T0.[ItemName], 
T0.[OnHand], 
T0.[AvgPrice],(T0.[OnHand]* T0.[AvgPrice])as'Value Of Stock'
etc.

Please note that this syntax does not allow you to use the traditional variables ([%0]) in the normal way. You can still have variables, but we can tell you how, if that is necessary.

Regards,

Johan

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Hi Johan

Thanks for your help

I have managed to combine the first query to show all four categories A, B, C & D into one query

I cant combine the others due to the difference in criteria

How do I get a Sub Total for each category?

SELECT'Product Category A',
T0.[ItemCode]as'Product Code', 
T0.[ItemName]as'Product Description', 
T0.[AvgPrice], 
T0.[OnHand],(T0.[OnHand]* T0.[AvgPrice])as'Value Of Stock'FROM OITM T0 
WHERE 
T0.[U_Category]='A'

UNIONALL

SELECT'Product Category B', 
T0.[ItemCode]as'Product Code', 
T0.[ItemName]as'Product Description', 
T0.[AvgPrice], 
T0.[OnHand],(T0.[OnHand]* T0.[AvgPrice])as'Value Of Stock'FROM OITM T0 
WHERE 
T0.[U_Category]='B'

UNIONALL

SELECT'Product Category C', 
T0.[ItemCode]as'Product Code', 
T0.[ItemName]as'Product Description', 
T0.[AvgPrice], 
T0.[OnHand],(T0.[OnHand]* T0.[AvgPrice])as'Value Of Stock'FROM OITM T0 
WHERE 
T0.[U_Category]='C'

UNIONALL

SELECT'Product Category D', 
T0.[ItemCode]as'Product Code', 
T0.[ItemName]as'Product Description', 
T0.[AvgPrice], 
T0.[OnHand],(T0.[OnHand]* T0.[AvgPrice])as'Value Of Stock'FROM OITM T0 
WHERE 
T0.[U_Category]='D'

Thanks

Rahul

0

Hi Rahul,

In your question your queries all have 5 columns, so you can combine them all with UNION ALL. The number of columns, and preferably the data type of each of these corresponding columns just needs to be the same. The rest of the query can be completely different from the next and/or previous one, different tables, different WHERE clause, grouping or no grouping, etc.

That means that you can use UNION ALL to add your subtotal line as well. You would get something like this:

SELECT 'Product Category A' AS 'Category',
T0.[ItemCode] as'Product Code', 
T0.[ItemName] as'Product Description', 
T0.[AvgPrice], 
T0.[OnHand],
(T0.[OnHand] * T0.[AvgPrice])as'Value Of Stock'
FROM OITM T0 
WHERE T0.[U_Category]='A'
UNION ALL
SELECT 'Product Category A total',
'' as'Product Code', 
'' as'Product Description', 
AVG(T0.[AvgPrice]) AS 'Average Price across all items',
SUM(T0.[OnHand]) AS 'Total Stock All Items',
SUM((T0.[OnHand] * T0.[AvgPrice])) as'Total Value Of Stock'
FROM OITM T0 
WHERE T0.[U_Category]='A'
UNION ALL
SELECT 'Product Category B',
etc.

Please note that the column titles in the result will always be derived from the top query. I added titles in the second query, just to give you an idea of what information the given column returns.

Regards,

Johan

0

Hi Johan

I used your suggestion and added one query at a time

First added the category A & B with the union all to calculate the totals and it worked perfectly

Then added all four categories A, B, C & D and it worked as well

Then I added the Finished Goods, Lamps and Blanks and I am getting an error and for the life of me i can't figure out where the error is

Please help

Thanks

Rahul

SELECT 'Product Category A' as 'Product Category',
T0.[ItemCode] as 'Product Code', 
T0.[ItemName] as 'Product Description', 
T0.[AvgPrice], 
T0.[OnHand], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 
FROM OITM T0 
WHERE 
T0.[U_Category] = 'A'
UNION ALL
SELECT 'Product Category A Total',
'' as 'Product Code',
'' as 'Product Description',
AVG(T0.[AvgPrice]) as 'Average Price Across All Items',
SUM(T0.[OnHand]) as 'Total Stock All Items',
SUM((T0.[OnHand]*T0.[AvgPrice])) as 'Total Value Of Stock'
FROM OITM T0
WHERE T0.[U_Category]='A'
UNION ALL

SELECT  'Product Category B' as 'Product Category', 
T0.[ItemCode] as 'Product Code', 
T0.[ItemName] as 'Product Description', 
T0.[AvgPrice], 
T0.[OnHand], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 
FROM OITM T0 
WHERE 
T0.[U_Category] = 'B'
UNION ALL
SELECT 'Product Category B Total',
'' as 'Product Code',
'' as 'Product Description',
AVG(T0.[AvgPrice]) as 'Average Price Across All Items',
SUM(T0.[OnHand]) as 'Total Stock All Items',
SUM((T0.[OnHand]*T0.[AvgPrice])) as 'Total Value Of Stock'
FROM OITM T0
WHERE T0.[U_Category]='B'
UNION ALL

SELECT  'Product Category C' as 'Product Category', 
T0.[ItemCode] as 'Product Code', 
T0.[ItemName] as 'Product Description', 
T0.[AvgPrice], 
T0.[OnHand], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 
FROM OITM T0 
WHERE 
T0.[U_Category] = 'C'
UNION ALL
SELECT 'Product Category C Total',
'' as 'Product Code',
'' as 'Product Description',
AVG(T0.[AvgPrice]) as 'Average Price Across All Items',
SUM(T0.[OnHand]) as 'Total Stock All Items',
SUM((T0.[OnHand]*T0.[AvgPrice])) as 'Total Value Of Stock'
FROM OITM T0
WHERE T0.[U_Category]='C'
UNION ALL

SELECT  'Product Category D' as 'Product Category', 
T0.[ItemCode] as 'Product Code', 
T0.[ItemName] as 'Product Description', 
T0.[AvgPrice], 
T0.[OnHand], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 
FROM OITM T0 
WHERE 
T0.[U_Category] = 'D'
UNION ALL
SELECT 'Product Category D Total',
'' as 'Product Code',
'' as 'Product Description',
AVG(T0.[AvgPrice]) as 'Average Price Across All Items',
SUM(T0.[OnHand]) as 'Total Stock All Items',
SUM((T0.[OnHand]*T0.[AvgPrice])) as 'Total Value Of Stock'
FROM OITM T0
WHERE T0.[U_Category]='D'
UNION ALL

SELECT 'Product Category - Finished Goods' as 'Product Category',
T0.[ItemCode] as 'Product Code', 
T0.[ItemName] as 'Product Description', 
T0.[AvgPrice], 
T0.[OnHand], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 
FROM OITM T0 
WHERE 
T0.[ItmsGrpCod] <> '287' AND  
T0.[ItmsGrpCod] <> '288' AND  
T0.[OnHand] >= 1  AND 
T0.[ItmsGrpCod] <> '289' 
UNION ALL
SELECT 'Product Category – Finished Goods Total',
'' as 'Product Code',
'' as 'Product Description',
AVG(T0.[AvgPrice]) as 'Average Price Across All Items',
SUM(T0.[OnHand]) as 'Total Stock All Items',
SUM((T0.[OnHand]*T0.[AvgPrice])) as 'Total Value Of Stock'
FROM OITM T0
T0.[ItmsGrpCod] <> '287' AND  
T0.[ItmsGrpCod] <> '288' AND  
T0.[OnHand] >= 1  AND 
T0.[ItmsGrpCod] <> '289' 
UNION ALL

SELECT 'Product Category - Lamps' as 'Product Category',
T0.[ItemCode] as 'Product Code', 
T0.[ItemName] as 'Product Description', 
T0.[AvgPrice], 
T0.[OnHand], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 
FROM OITM T0 
WHERE 
T0.[ItmsGrpCod] <> '287' AND  
T0.[ItmsGrpCod] <> '288' AND  
T0.[OnHand] >= 1 AND 
T0.[ItmsGrpCod] = '289'
UNION ALL
SELECT 'Product Category - Lamps Total',
'' as 'Product Code',
'' as 'Product Description',
AVG(T0.[AvgPrice]) as 'Average Price Across All Items',
SUM(T0.[OnHand]) as 'Total Stock All Items',
SUM((T0.[OnHand]*T0.[AvgPrice])) as 'Total Value Of Stock'
FROM OITM T0
T0.[ItmsGrpCod] <> '287' AND  
T0.[ItmsGrpCod] <> '288' AND  
T0.[OnHand] >= 1 AND 
T0.[ItmsGrpCod] = '289'
UNION ALL

SELECT 'Product Category – Blanks' as 'Product Category',
T0.[ItemCode] as 'Product Code', 
T0.[ItemName] as 'Product Description',  
T0.[AvgPrice], 
T0.[OnHand], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 
FROM OITM T0 
WHERE 
T0.[ItmsGrpCod] = '287' AND  
T0.[U_Category] IS NULL AND  
T0.[OnHand] > 0
UNION ALL
SELECT 'Product Category - Blank Total',
'' as 'Product Code',
'' as 'Product Description',
AVG(T0.[AvgPrice]) as 'Average Price Across All Items',
SUM(T0.[OnHand]) as 'Total Stock All Items',
SUM((T0.[OnHand]*T0.[AvgPrice])) as 'Total Value Of Stock'
FROM OITM T0
WHERE 
T0.[ItmsGrpCod] = '287' AND  
T0.[U_Category] IS NULL AND  
T0.[OnHand] > 0
<br>
0

Hi Rahul,

When queries get this long and/or complicated, it is best to use MS SQL Studio to write the query. It is easier to debug.

In this case it seems that you forgot the word WHERE in previous-to-last query set:

SUM((T0.[OnHand]*T0.[AvgPrice])) as 'Total Value Of Stock'
FROM OITM T0
T0.[ItmsGrpCod] <> '287' AND  etc.

Regards,

Johan

0

Hi Johan

It worked

Thanks for your help

Much appreciated

Rahul

1