Sum one field in different fields based on conditions on another field with group by

[mehdi483]

Hi! I'm a beginner user of mssql and I've been stuck in a report that I'm trying to make.
I have a table with fields like 'PartName', 'VoucherQuantit y', 'VoucherType', 'VoucherDate', and 'DLName'. I want to get the sum of 'VoucherQuantit y' and I want its results in different fields based on different values of 'VoucherType' (which for example can be 01, 02, 03, or 04) and I want the result to be grouped by 'PartName' AND 'DLName'. Obviously I want this sums WHERE 'VoucherDate' IS BETWEEN a and b.
I've been trying different queries for a while and I have tried creating different views to solve this but so far I have got zilch. I'd really appreciate it a lot if anyone can help me!

[Rabbit]

I have trouble understanding your question. Some sample data and results would help.

[mehdi483]

Thanks for answering dear Rabbit.

I have attached the data set and result set in an excel file.
I hope those tables are clear enough. For every PartName-DlName combination, I want to get the sum of the quantities of vchtype=01 in one field and sum of the quantities of vchtype=02 in another field.

[Rabbit]

I don't open attachments from people I don't know. Please post some sample data and results in the thread itself.

[mehdi483]

VchID VchType VchQty PartName DlName

001 01 10 Book David
002 02 5 Pen David
003 01 12 Book David
004 02 15 Pen David
005 01 30 Pen David
006 02 2 Book David
007 01 19 Pen David
008 02 16 Book David
009 01 17 Book Jack
010 02 14 Pen Jack
011 01 32 Book Jack

[mehdi483]

VchID||||VchTyp e| VchQty||PartNam e |DlName
001 |||||01 ||||| 10 |||||Book |||||David
002 |||||02 ||||| 5 |||||Pen |||||David
003 |||||01 ||||| 12 |||||Book |||||David
004 |||||02 ||||| 15 |||||Pen |||||David
005 |||||01 ||||| 30 |||||Pen |||||David
006 |||||02 ||||| 2 |||||Book |||||David
007 |||||01 ||||| 19 |||||Pen |||||David
008 |||||02 ||||| 16 |||||Book |||||David
009 |||||01 ||||| 17 |||||Book |||||Jack
010 |||||02 ||||| 14 |||||Pen |||||Jack
011 |||||01 ||||| 32 |||||Book |||||Jack

[mehdi483]

sorry about the last reply. texts all got mixed up, so I took a screenshot from the sets:

This is my dataset:


and I want to get this result set:

[r035198x]

'DLName'. I want to get the sum of 'VoucherQuantit y' and I want its results in different fields based on different values of 'VoucherType'

select VoucherType, sum(VoucherQuantity) from your table name group by VoucherType

I want the result to be grouped by 'PartName' AND 'DLName'.

Do you really mean grouped by 'PartName' AND 'DLName' or ordered by 'PartName' AND 'DLName'? If you mean grouped by then

select VoucherType, PartName,DLName sum(VoucherQuantity) from your table name group by VoucherType, PartName,DLName

Obviously I want this sums WHERE 'VoucherDate' IS BETWEEN a and b.

Then you just add the where clause before the group by clause.

I've been trying different queries for a while and I have tried creating different views to solve this but so far I have got zilch.

Next time you should include the query that you tried so people can help you with where you went wrong.

[mehdi483]

thank you dear r035198x! you're right about posting my own script. This is the script I used (forget about the VoucherDate):

SELECT
PartName, DLName, (SELECT sum(quantity) WHERE VoucherType = '01' GROUP BY PartName, DLName), (SELECT sum(quantity) WHERE VoucherType = '02' GROUP BY PartName, DLName), SELECT sum(quantity) WHERE VoucherType = '03' GROUP BY PartName, DLName)
GROUP BY PartName, DLName

By the way, your answer would have been right if the sums of quantities of different VoucherTypes didn't come in different records

[r035198x]

You don't have any from clauses in your inner queries. Also, if your inner selects are doing the grouping then you don't need to group in the outer select. Your required results are then simply results of separate selects that are not really connected and you don't need the outer select at all.

[mehdi483]

Yes, I know that. But what can I do?

[r035198x]

Add the from clauses to your innner selects, remove the PartName, DLName and the group by clause from your outer select.

[mehdi483]

Dear r035198x
I tried it your way:

SELECT
(SELECT sum(Quantity) AS QTY01 FROM VoucherTable WHERE VoucherType = '01' GROUP BY PartName, DLName),
(SELECT sum(Quantity) AS QTY02 FROM VoucherTable WHERE VoucherType = '02' GROUP BY PartName, DLName),
(SELECT sum(Quantity) AS QTY03 FROM VoucherTable WHERE VoucherType = '03' GROUP BY PartName, DLName),
(SELECT sum(Quantity) AS QTY04 FROM VoucherTable WHERE VoucherType = '04' GROUP BY PartName, DLName)
FROM VoucherTable

but I got this error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

[r035198x]

Ya well that won't work. You need to visualize the results that you want first so that it makes sense. As it is you are trying to mix column data with row data. How do you intend to display those results in one table? As I understand it you have four results that you want to combine vertically. If your grouping columns had unique values then you would join the individual results but it doesn't look like it so why not get the 4 results using 4 different queries?

[mehdi483]

Well cause I need the results in this format: