I need some help with this simple query !!

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

SQL Server 2005 Forums

Transact-SQL (2005)

I need some help with this simple query !!

Author

Topic

chayolle
Starting Member

13 Posts

Posted - 2008-03-10 : 01:18:57

Hi,

I have this query:


SELECT ('Section : ' + F.Section  + '   ' + (SELECT S.SectionName FROM SFM_Section AS S
        WHERE S.Company = F.Company
        AND S.Department    = F.Department
        AND  S.Section = F.Section ) ),
F.Variety, F.Category,
isnull(SUM(F.AreaCutCurrent), 0) AS AreaCutCurrent,
isnull(SUM(F.TonnageCurrent), 0) AS TonnageCurrent,
isnull(SUM(F.YieldCurrent), 0)   AS YieldCurrent
FROM SFM_Field AS F
WHERE F.CropYear = 2007 and ActiveFlag = 1 and Section = 04
GROUP BY F.Company, F.Department, F.Section, F.Variety, F.Category
ORDER BY F.Section, F.Variety, F.Category

that returns me the following results:

Section:04 Region Haute|M0052/78|R07|1.84 |137.64 |74.804
Section:04 Region Haute|M0695/69|R01|37.71|2817.65|434.009
Section:04 Region Haute|M0695/69|R02|35.08|2282.33|523.234
Section:04 Region Haute|M0695/69|R05|21.2 |1563.01|147.579
Section:04 Region Haute|M0695/69|R09|6.52 |484.39 |74.293
Section:04 Region Haute|M0695/69|R11|0 |0 |0
Section:04 Region Haute|M1397/86|R04|8.35 |793.61 |190.141
Section:04 Region Haute|M1400/86|GS |18.03|2093.91|116.135

What should I do if I don't want the records that the sum is equal to 0 to appear in my results? (Like the 3rd row from the end)

Thanks for your help

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-10 : 02:07:14

select * from (

Your main query comes here

)t
where AreaCutCurrent<>0 or TonnageCurrent<>0 or YieldCurrent<>0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-10 : 03:07:53

quote:
What should I do if I don't want the records that the sum is equal to 0

Ignore if all of the sum is 0 or either one is 0 ?


WHERE F.CropYear = 2007 and ActiveFlag = 1 and Section = 04
GROUP BY F.Company, F.Department, F.Section, F.Variety, F.Category
HAVING isnull(SUM(F.AreaCutCurrent), 0) <> 0
OR     isnull(SUM(F.TonnageCurrent), 0) <> 0
OR     isnull(SUM(F.YieldCurrent), 0)   <> 0
ORDER BY F.Section, F.Variety, F.Category

Depending on your requirement change the OR to AND.

KH
[spoiler]Time is always against us[/spoiler]

chayolle
Starting Member

13 Posts

Posted - 2008-03-10 : 03:29:38

thx kh, i think i was looking for something really complicated and it was an easy issue !!! thx anyway

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-10 : 03:52:39

You can also use ayamas's method to do that.

KH
[spoiler]Time is always against us[/spoiler]