Thread: SQL where clause help

Hi All,

Ok, I want the sum of either JobDescID = 1 thru 11 or any JobDesc containing the word "Field"....I tried the following but not getting the correct results.

this is a new field in my select statement query....

Code:

  (SELECT     SUM(ActThreeMoAgo)
                            FROM          vwPlanning B
                            WHERE      LocID = B.LocID AND JobDescID = 1 OR
                                                   LocID = B.LocID AND JobDescID = 2 OR
                                                   LocID = B.LocID AND JobDescID = 3 and so til 11... :o) AS SumAllField

my results for LocID 1 should be 33 but its returning 3003

also tried...

Code:

 (SELECT     SUM(ActThreeMoAgo)
                            FROM          vwPlanning B
                            WHERE      LocID = B.LocID AND JobDesc = '%Field%') AS SumAllField

results come back NULL ....

Thanks for any help with this....

Ok, Thanks w0lffy

Still getting the same funny results but my next question...I'm sum an already summed field in the same query, is this ok?

Ex:

Code:

my select statement has 
select Sum(ActThreeMoAgo) as ActThreeMoAgo, more fields...etc., then I have this as the last field...,  (SELECT     SUM(ActThreeMoAgo)
                            FROM          vwPlanning B
                            WHERE      LocID = B.LocID AND JobDesc like '%Field%') AS SumAllField

then from and where and groupby...

Originally Posted by Wolffy

If I understand the query correctly no you can't use the results of an aggregate in the same select statement. If you need a sum of sum....

Code:

Select sum(subTotal) as GrandTotal
  From (Select sum(lineItems) as subTotal)

or in SQL 2005 and up

Code:

With CTE as (
  Select sum(lineItems) as subTotal
) Select sum(subTotal) as GrandTotal From CTE

ok, so if I have the correct results I want for the first field Sum(ActThreeMoAgo) as ActThreeMoAgo, and I get values 10, 20, and 3, then if I want to get sum 33 in new column i should create new query?

Ok, let me look at what you are suggesting.

I have good results but I need to get more results from those results.

ex:
without new fields wanting

Code:

LocID-JobDescID-JobDesc-Act3MoAgo-Act2MoAgo-Act1MoAgo-LastFcast..etc.
1-1-Field1-10-8-7-8
1-2-Field2-20-9-9-9
1-3-Field3-0-1-1-1
1-4-Field4-3-2-3-3
1-5-Field5-0-1-1-1
1-14-Admin-11-9-9-9
1-15-Support-15-13-13-20

I would like to have a new column that will give me the sum of all in LocID = 1 and JobDescID between 1 and 11..so an extra column would be added at the right end and show value 33 (according to items in blue highlighted above).