Sum with If/Then Scenario - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Sum with If/Then Scenario

Author

Topic

warrend
Starting Member

15 Posts

Posted - 2012-08-15 : 13:45:49

I am currently working in SQL 2005 and just starting into the more difficult SQL projects. I need to get the below to work within a query. They are pulling from the same column but based on different criteria (to get a count/sum of the type).

SELECT
Sum(IIf([dbo.stops.stp_type]="PUP",1,0)) AS PickUp,
Sum(IIf([dbo.stops.stp_type]="DRP",1,0)) AS [Drop]

I have other criteria I am looking at but they are straight forward select/where statements.

NOTE: Received this based on SQL statement within MS Access query.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-15 : 13:48:52

Do you have a question?

warrend
Starting Member

15 Posts

Posted - 2012-08-15 : 13:55:56

How do I get the Select statement to work? Is there something wrong in what I have? Does it need to be broken out another way?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-15 : 14:20:45

What doesn't work about it? Are you getting an error or the wrong results? You need to realize that we have no insight into your database or your environment. Below are some links that might help you prepare your question in a way that we can help you.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Here is a quick guess:

SELECT 
    Sum(IIf([stp_type]='PUP', 1, 0)) AS PickUp, 
    Sum(IIf([stp_type]='DRP', 1, 0)) AS [Drop]
FROM
    dbo.stops

warrend
Starting Member

15 Posts

Posted - 2012-08-15 : 15:02:50

I'm sorry, forums are new to me as well. Does this help?

I need to get a sum of PUP stp_type and sum of DRP stp_type into two separate columns (preferable renaming the columns).

Example information from my dbo.stops table. There isn’t a primary key (this program db has several tables without a primary key) but the cmp_id is a foreign key in the table.

stp_type ord_hdrnumber stp_number cmp_id
PUP 988304 3604849 IDETLA
DRP 988304 3604850 AMEHUT04
NONE 988304 3604851 EXPLAR02
PUP 987799 3604839 SUMBALA0
DRP 987799 3604840 WALMAR54
PUP 987796 3604837 SUMBALA0
DRP 987796 3604838 WALMAR53
PUP 987795 3604833 SUMBALA0
DRP 987795 3604834 WALMAR52

I have tried these

SELECT
Sum(IIf([dbo.stops.stp_type]=’PUP’,1,0)) AS PickUp,
Sum(IIf([dbo.stops.stp_type]=’DRP’,1,0)) AS [Drop]
From stops
(Returned error: Incorrect syntax near '='.)

And

SELECT
Sum(IIf([stp_type]='PUP', 1, 0)) AS PickUp,
Sum(IIf([stp_type]='DRP', 1, 0)) AS [Drop]
FROM
dbo.stops
(Returned error: Incorrect syntax near '='.)

So my query should return something similar to below based on the above table.

Pickup Drop
4 4

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-15 : 15:21:55

quote:
Originally posted by warrend

<snip>
NOTE: Received this based on SQL statement within MS Access query.

Are you using Access or SQL server? If Access, then you might need to go to an Access forum or someone else might be able to help you.

warrend
Starting Member

15 Posts

Posted - 2012-08-15 : 15:35:00

I am using SQL 2005. This originally started as an Access report, we are trying to automate it through SQL. So far, the rest of the data is working, I'm only getting an error on this portion of the select statement.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-15 : 15:43:23

Oh, 2005 Doesn't support the new IIF function. Try using a CASE expression:

SELECT 
    Sum(CASE WHEN [stp_type]='PUP' THEN 1 ELSE 0 END) AS PickUp, 
    Sum(CASE WHEN [stp_type]='DRP' THEN 1 ELSE 0 END) AS [Drop]
FROM
    dbo.stops

warrend
Starting Member

15 Posts

Posted - 2012-08-15 : 15:51:11

That worked! Thank you for all the help and patience. I WILL get better at all of this.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-15 : 15:59:07

No problem. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources