Query Different Results - 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

Query Different Results

Author

Topic

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-03-12 : 16:48:46

If I run the same queries i get different results. The first query I dont specified the promotioncode, I just filter the results in excel to the promotioncode I want. The second query I specify the promotion code but I get different numbers.

The first query I run is

[CODE]

Select month(a.calldate), a.callvendorcode, a.promotioncode, count(*) 'AFGA'
from history.dbo.historyrecords a, Type.dbo.TypeRecords b
where a.recordid= b.recordid
and a.calldate between '20100201' and '20100228'
--and a.CallDate >= '20100220'
and a.Statein ('In', 'Order')
and a.Priceplan <> ''
and (a.priceplan <> b.price_plan)
and a.priceplan in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
'73216','73217','73218','73219','73220','73221','73222','73223','73224','73225','73226','73227','73228','73229','73230','73231',
'73232','73430','73431','73432','73433','73434','73435','73442','73443','73444','73445','73446','73448','73469','73470','73471',
'73472','73473','73474','73478','73479','73481','73482','73484','73485','73487','73488','73490','73491','73493','73494','83235',
'73946','73947','73956','73957','73958','73959','73960','73961','73962','74558','74566','74569','74577','74591','74592','74593')
and b.price_plan not in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
'73216','73217','73218','73219','73220','73221','73222','73223','73224','73225','73226','73227','73228','73229','73230','73231',
'73232','73430','73431','73432','73433','73434','73435','73442','73443','73444','73445','73446','73448','73469','73470','73471',
'73472','73473','73474','73478','73479','73481','73482','73484','73485','73487','73488','73490','73491','73493','73494','83235',
'73946','73947','73956','73957','73958','73959','73960','73961','73962','74558','74566','74569','74577','74591','74592','74593')

group by month(a.calldate), a.callvendorcode, a.promotioncode

Then i run the same query just just specifying the promotioncode
[/CODE]

[CODE]
from history.dbo.historyrecords a, Type.dbo.TypeRecords b
where a.recordid= b.recordid
and a.calldate between '20100201' and '20100228'
--and a.CallDate >= '20100220'
and a.Statein ('In', 'Order')
and a.PromotionCode IN ('AV123', 'AV127', 'AV143')
and a.Priceplan <> ''
and (a.priceplan <> b.price_plan)
and a.priceplan in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
'73216','73217','73218','73219','73220','73221','73222','73223','73224','73225','73226','73227','73228','73229','73230','73231',
'73232','73430','73431','73432','73433','73434','73435','73442','73443','73444','73445','73446','73448','73469','73470','73471',
'73472','73473','73474','73478','73479','73481','73482','73484','73485','73487','73488','73490','73491','73493','73494','83235',
'73946','73947','73956','73957','73958','73959','73960','73961','73962','74558','74566','74569','74577','74591','74592','74593')
and b.price_plan not in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
'73216','73217','73218','73219','73220','73221','73222','73223','73224','73225','73226','73227','73228','73229','73230','73231',
'73232','73430','73431','73432','73433','73434','73435','73442','73443','73444','73445','73446','73448','73469','73470','73471',
'73472','73473','73474','73478','73479','73481','73482','73484','73485','73487','73488','73490','73491','73493','73494','83235',
'73946','73947','73956','73957','73958','73959','73960','73961','73962','74558','74566','74569','74577','74591','74592','74593')
[/CODE]
group by month(a.calldate), a.callvendorcode,

Sachin.Nand

2937 Posts

Posted - 2010-03-13 : 01:41:36

No an give you any advice with what you posted.Please post some sample data.Since you have a less group by clause in the second query the result will differ.see the difference


declare @tbl1 as table(id int primary key  ,name varchar(40))
insert into @tbl1
select 1 ,'abc' union all
select 2 ,'def' union all
select 3,'def'

select id,COUNT(*),name from @tbl1
group by id,name

select name,COUNT(*) from @tbl1
group by name

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 01:51:31

he has a , after last field in second group by so not sure if he missed it while posting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources