SUM Function in SQL Statement for diffentent where clauses

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi !

I am working on SQL Server 2008 Database.

I show you a table with data and then i show you what a result i want then suggest me the best solution.

Table Name tbl_Inventory
following shows the data in table.

Tansaction	ItemName	Quantity	Status
In on 1/1/2015	Square Pipe	15	Ordered
In on 1/1/2015	Square Pipe	10	Ordered
In on 1/1/2015	Square Pipe	20	Ordered
In on 1/1/2015	Square Pipe	5	Loaded
In on 1/1/2015	Square Pipe	12	Loaded
In on 1/1/2015	Square Pipe	8	Loaded
In on 1/1/2015	Square Pipe	16	Shipped
In on 1/1/2015	Square Pipe	20	Shipped
In on 1/1/2015	Square Pipe	7	Shipped
In on 1/1/2015	Square Pipe	25	Billed
In on 1/1/2015	Square Pipe	9	Billed
In on 1/1/2015	Square Pipe	11	Billed
In on 1/1/2015	Round Pipe	40	Ordered
In on 1/1/2015	Round Pipe	10	Loaded
In on 1/1/2015	Round Pipe	24	Loaded
In on 1/1/2015	Round Pipe	16	Loaded
In on 1/1/2015	Round Pipe	32	Shipped
In on 1/1/2015	Round Pipe	40	Shipped
In on 1/1/2015	Round Pipe	14	Shipped
In on 1/1/2015	Round Pipe	50	Billed
In on 1/1/2015	Round Pipe	18	Billed
In on 1/1/2015	Round Pipe	22	Billed

Now i want the following result

ItemName      OrderedQty   LoadedQuantity   ShippedQuantity   BilledQuantity
SquarePipe        45             25               43              45  
RoundPipe         90             50               86              90

i hope you will understand although there is some problem in table format but i think the basic concept is clear what i want.

please help me

thanks

Posted 22-Aug-15 5:07am

Muhammad Asim Mughal

Updated 22-Aug-15 5:35am

OriginalGriff

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Homero Rivera · Answer 1 · 2015-08-22T05:44:00

One way to do this is...

SQL

SELECT ItemName
, SUM(CASE [Status] WHEN 'Ordered' THEN Quantity ELSE 0 END) as OrderedQty 
, SUM(CASE [Status] WHEN 'Loaded' THEN Quantity ELSE 0 END) as LoadedQuantity 
, SUM(CASE [Status] WHEN 'Shipped' THEN Quantity ELSE 0 END) as ShippedQuantity 
, SUM(CASE [Status] WHEN 'Billed' THEN Quantity ELSE 0 END) as  BilledQuantity
FROM tbl_Inventory
GROUP BY ItemName

Wendelius · Answer 2 · 2015-08-22T05:33:00

What you're after is called a PIVOT. The query could look something like:

SQL

select *
from (select itemname, quantity, status from tablename) a
pivot (
   sum(quantity)
   for status in (['Ordered'], ['Loaded'],['Shipped'],['Billed']) 
) as amount

For more information, see https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]

SUM Function in SQL Statement for diffentent where clauses

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0