SQL 2000 Equivalent of Access Sum(IIf()) Required

[BrynJ]

Hoping someone can help me out here, I'm a bit stuck. We're moving our manufacturing system over to a new version, previously it had an Access front end with SQL2000 database, but the latest version is SQL 2000 only. I've created loads of custom queries in the past for various reports and now I'm having to recreate them through SQL stored procedures, views and user functions.

Several of the queries use VBA Sum IIfs to artificially create groups of items such as part numbers - is there any way I can recreate queries of this type natively in SQL 2000? I'm sure it must be possible but unsure of syntax etc - I've looked at IFs and CASE but I can't get it right.

I'd be very grateful if someone could point me in the right direction, I've tried googling the problem but can find very little info out there. Many thanks for any help offered.

Cheers,

Bryn

[Crazy Squirrel]

My brain is a bit dead at the moment hence this pretty useless example, but it shows the syntax.

SELECT (CASE users.name WHEN 'FRED' THEN 1 ELSE 2 END) as 'Thingy'
FROM users

That would return rows with the field 'Thingy' set to 1 if name is fred, else 2. The ELSE part of the query is of course optional.

[Paddy]

Nested query with

select sum(field)
from table
where condition

off the top of my head :)

[BrynJ]

Thanks for the help chaps. Crazy Squirrel - to cover your suggestion first, through experimenting I'd kind of got something similar myself but what I don't know how to do is then sum the results - is there any way of doing this directly, or will I have to put the select into a user function that returns the result as a table and then run another group select query on this result?

Paddy - what is the format for nested queries in SQL2000? I've done a search on google but I can't find the syntax - I'm guessing something like this?:

SELECT (select sum(field) from table where condition) AS fieldname1, etc

..but that doesn't work? Very confused :help:

[Tyler Durden]

select sum(field) as TotalWhatever from table where condition

[Crazy Squirrel]

You can do:

SELECT sum((CASE users.name WHEN 'FRED' THEN 1 ELSE 2 END)) as 'Thingy'
FROM users

That would do the same as my example before but instead of a row for each item you'd just get the sum of all the 1s and 2s added together.

I need to see what your actually trying to do, i.e. some example tables and desired output. Should be easy enough then.

[BrynJ]

Thanks again for all the help - Crazy Squirrel, your suggestion worked perfectly :)

Here's an example query, just so you can get an idea of what I needed to do:


SELECT dbo.MasterSchedule.DueDate, SUM((CASE LEFT(dbo.Parts.PartNumber, 2) WHEN 'TG' THEN 1 ELSE 0 END)) AS DailyTotal,
SUM((CASE LEFT(dbo.Parts.PartNumber, 5) WHEN 'TG672' THEN 1 ELSE 0 END)) AS HB401, SUM((CASE LEFT(dbo.Parts.PartNumber, 5)
WHEN 'TG673' THEN 1 ELSE 0 END)) AS HB401Basic, SUM((CASE LEFT(dbo.Parts.PartNumber, 7) WHEN 'TG66210' THEN 1 ELSE 0 END)) AS HB505,
SUM((CASE LEFT(dbo.Parts.PartNumber, 6) WHEN 'TG6625' THEN 1 ELSE 0 END)) AS HB505Classic, SUM((CASE LEFT(dbo.Parts.PartNumber, 7)
WHEN 'TG66211' THEN 1 ELSE 0 END)) AS HB505VD, SUM((CASE LEFT(dbo.Parts.PartNumber, 5) WHEN 'TG663' THEN 1 ELSE 0 END))
AS HB505Basic, SUM((CASE LEFT(dbo.Parts.PartNumber, 7) WHEN 'TG65210' THEN 1 ELSE 0 END)) AS HB510,
SUM((CASE LEFT(dbo.Parts.PartNumber, 7) WHEN 'TG65211' THEN 1 ELSE 0 END)) AS HB510VD, SUM((CASE LEFT(dbo.Parts.PartNumber, 6)
WHEN 'TG6525' THEN 1 ELSE 0 END)) AS HB510Classic, SUM((CASE LEFT(dbo.Parts.PartNumber, 3) WHEN 'TG5' THEN 1 ELSE 0 END))
AS Eurolight
FROM dbo.MasterSchedule INNER JOIN
dbo.Parts ON dbo.MasterSchedule.PartID = dbo.Parts.PartID
WHERE (dbo.MasterSchedule.MPSTypeCode = 1)
GROUP BY dbo.MasterSchedule.DueDate


I did actually manage to get something working yesterday, via a view with the CASE conditions and GROUP BY stored procedure with the view as a data source - but the above solution is a lot tidier. Cheers :)

Bryn

[Crazy Squirrel]

Ahh thought that could be what you were doing, the else is of course optional so:

SUM((CASE LEFT(dbo.Parts.PartNumber, 2) WHEN 'TG' THEN 1 END)) AS DailyTotal

will work and be slightly shorter. :p
For this situation you can also use COUNT instead of SUM but you have to leave the ELSE off so it only counts the non null returns.:

COUNT((CASE LEFT(dbo.Parts.PartNumber, 2) WHEN 'TG' THEN 1 END)) AS DailyTotal

I reckon COUNT would be slightly faster - if it matters to you.

[Paddy]

Em... surely this gives you a count effect rather than a sum effect. :thinking:

[Crazy Squirrel]

What BrynJ's query is doing is counting the number of parts with certain part numbers. Anything that matches what he wants is a 1, anything that doesn't is a 0, for each check he does. The Sum of the field would be something like 1+1+0+0+1+0 = 3
If you do it with a count and set the result to 1 or even "A" (anything that isn't null) on a match and null for a non match (either by saying else null or just leaving out the else alltogether) then the non-matches are not counted as they are nulls, i.e. 1 & 1 & null & null & 1 & null = 3.

[BrynJ]

Cheers for the further assistance Crazy Squirrel - I'll change the query as suggested, anything that makes it clearer and faster is worthwhile in my book :)

Bryn.