doubt in this query - 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

doubt in this query

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-09-28 : 15:37:21

Hi All,
There are 2 possible values of MeterID.
The difference between below 1st and (2nd and 3rd) queries is that, I have hardcoded meterid in the 2nd and 3rd query while i am taking it from another table in the 1st one. If I execute the 1st query, more rows are returned(which is incorrect) but if I execute the 2nd one or 3rd one individually, only 2 rows are returned for each(which is correct). I understand that in the 1st query, the clause 'GROUP BY BeginDT, EndDT
HAVING count(*) > 1' has no effect that is why it returns more rows. Can anybody tell me why is it that this clause has no effect in the 1st query?

1.>
SELECT BeginDT, EndDT, COUNT(*)
FROM IntervalKWH where DATEPART(DAY,BeginDT)=6 and MeterID in
(SELECT distinct MeterID
FROM Table_1
GROUP BY DayOfWeek, MeterID, HourEnd,HourInterval
HAVING count(*) > 1)
GROUP BY BeginDT, EndDT
HAVING count(*) > 1

2.>
SELECT BeginDT, EndDT, count(*)
FROM IntervalKWH where MeterID=10112344 and DATEPART(DAY,BeginDT)=6
GROUP BY BeginDT, EndDT
HAVING count(*) > 1

3.>
SELECT BeginDT, EndDT, count(*)
FROM IntervalKWH where MeterID=10112345 and DATEPART(DAY,BeginDT)=6
GROUP BY BeginDT, EndDT
HAVING count(*) > 1

Thanks in anticipation,
Mavericky

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-28 : 15:52:40

If you run the IN-clause query how many distinct MeterIDs are returned?

SELECT distinct MeterID
FROM Table_1
GROUP BY DayOfWeek, MeterID, HourEnd,HourInterval
HAVING count(*) > 1

mavericky
Posting Yak Master

117 Posts

Posted - 2011-09-28 : 15:55:32

2 MeterIDs : 10112344 and 10112345 . I also tried the 1st query like this:

SELECT BeginDT, EndDT, COUNT(*)
FROM IntervalKWH where DATEPART(DAY,BeginDT)=6 and MeterID in
(10112344,1012345
HAVING count(*) > 1)
GROUP BY BeginDT, EndDT
HAVING count(*) > 1

But still it returns more rows than 2nd and 3rd queries.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-28 : 17:01:37

Perhaps I'm missing something but, you are asking for two MeterIDs. Would you expect to get more results than when only use one MeterID as a predicate?

mavericky
Posting Yak Master

117 Posts

Posted - 2011-09-28 : 17:46:20

what i am saying is, these are the results returned:
2nd Query: 2 records
3rd Query: 2 records
1st Query: should ideally give 4 records, but it gives 98 records.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-29 : 12:23:19

I can think of several reasons why you might get many more rows returned for Query1. But, without knowing your data it's hard to say what's going on. Also, I'm not sure what your query is supposed to accomplish, but what if you add the MeterID to the GROUP BY clause?

SELECT MeterID, BeginDT, EndDT, COUNT(*)
FROM IntervalKWH where DATEPART(DAY,BeginDT)=6 and MeterID in
(10112344,1012345
HAVING count(*) > 1)
GROUP BY MeterID, BeginDT, EndDT 
HAVING count(*) > 1

If you want more assistance you'll have to post some sample data, in a consumable format, that shows the issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources