Multiple Group By with a Count

My goal is to count all the distinct nodeIDs grouped by classification and date applied. I have to use distinct NodeIDs because there are multiple rows of the same NodeID for the same date with the same ClassificationID. Not sure why this is but it is how the data appears in the table.

I run the following SQL and get the following results:

Select ClassificationID, count(DISTINCT NodeID)
from ADVClassApplied adv with (NOLOCK)
where DateApplied between '06-01-2016' and '07-01-2016' and ClassificationID in (106332120, 167084905, 288477278)
group by ClassificationID
Order By 1, 2

 106332120   24245  167084905     632  288477278    5446

I add my Date field and run the adjusted SQL and get the following results:

Select ClassificationID, left(dateapplied,11) as Date, count(DISTINCT NodeID)
from ADVClassApplied adv with (NOLOCK)
where DateApplied between '06-01-2016' and '07-01-2016' and ClassificationID in (106332120, 167084905, 288477278)
group by ClassificationID, left(dateapplied,11)
Order By 1, 2

 106332120   Jun 3 2016    1663  106332120   Jun 7 2016   22915  106332120   Jun 8 2016     250                           24828 167084905   Jun 3 2016    50  167084905   Jun 7 2016   599  167084905   Jun 8 2016     5                           644 288477278   Jun 7 2016   5353  288477278   Jun 8 2016     93                          5446

I did the totalling by hand but you can see that for two of the three ClassificationIDs the total is different. I know this has to do with the way I am doing my group bys. But I can't figure out what is wrong. I have tried changing the order of the group bys and that didn't make a difference. I just have to make sure the numbers are right here and I am fairly certain they are not.

Find more posts tagged with

Comments

Lindsay_Davies

Hi Greg,

You are counting distinct nodeids WITHIN each reported date.

A nodeid may being reported on more than one line because it was classified on more than one date in the range.

If you want to see some detail of those, try this adapted query…

Select ClassificationID, left(dateapplied,11) as Date,Nodeid, count(*)
from ADVClassApplied adv with (NOLOCK)
where DateApplied between '06-01-2016' and '07-01-2016' and ClassificationID in (106332120, 167084905, 288477278)
group by ClassificationID, left(dateapplied,11) , NodeID
HAVING count(*) >1
Order By 1, 2, 4 desc

This will show you those nodeids which occur more than once.

Hope this helps explain things.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES
D +44 (0)1923 299 378

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 09 June 2016 15:43
To: eLink Recipient
Subject: Multiple Group By with a Count

Multiple Group By with a Count

Posted by Kellogg, Greg On 06/09/2016 10:32 AM

I run the following SQL and get the following results:

 106332120   24245

 167084905     632

 288477278    5446

I add my Date field and run the adjusted SQL and get the following results:

 106332120   Jun 3 2016    1663

 106332120   Jun 7 2016   22915

 106332120   Jun 8 2016     250

 167084905   Jun 3 2016    50

 167084905   Jun 7 2016   599

 167084905   Jun 8 2016     5

 288477278   Jun 7 2016   5353

 288477278   Jun 8 2016     93

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

CONFIDENTIALITY NOTICE: This e-mail, including any attachments, contains information that may be confidential, and is protected by copyright. It is directed to the intended recipient(s) only. If you have received this e-mail in error please e-mail the sender by replying to this message, and then delete the e-mail. Unauthorised disclosure, publication, copying or use of this e-mail is prohibited. Any communication of a personal nature in this e-mail is not made by or on behalf of any RES group company. E-mails sent or received may be monitored to ensure compliance with the law, regulation and/or our policies.

Greg Kellogg

Thanks Lindsay. When I run it as you posted it I get No Results Found.

Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200

the iQ Business Group

intelligence. applied.

www.IQBGinc.com

From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>
Sent: Thursday, June 9, 2016 3:03:08 PM
To: eLink Recipient
Subject: RE Multiple Group By with a Count

RE Multiple Group By with a Count

Posted by Davies, Lindsay On 06/09/2016 04:01 PM

Hi Greg, You are counting distinct nodeids WITHIN each reported date.A nodeid may being reported on more than one line because it was classified on more than one date in the range. If you want to see some detail of those, try this adapted query…Select ClassificationID, left(dateapplied,11) as Date,Nodeid, count(*)
from ADVClassApplied adv with (NOLOCK)
where DateApplied between '06-01-2016' and '07-01-2016' and ClassificationID in (106332120, 167084905, 288477278)
group by ClassificationID, left(dateapplied,11) , NodeID
HAVING count(*) >1
Order By 1, 2, 4 descThis will show you those nodeids which occur more than once.Hope this helps explain things.RegardsLindsay Lindsay Davies
Information Systems Administrator
RES
D +44 (0)1923 299 378

Multiple Group By with a Count

Posted by Kellogg, Greg On 06/09/2016 10:32 AM

I run the following SQL and get the following results:

 106332120   24245

 167084905     632

 288477278    5446

I add my Date field and run the adjusted SQL and get the following results:

 106332120   Jun 3 2016    1663

 106332120   Jun 7 2016   22915

 106332120   Jun 8 2016     250

 167084905   Jun 3 2016    50

 167084905   Jun 7 2016   599

 167084905   Jun 8 2016     5

 288477278   Jun 7 2016   5353

 288477278   Jun 8 2016     93

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Multiple Group By with a Count
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

****** CONFIDENTIALITY NOTICE ****** This message and any attachments are confidential and for the sole use of the intended recipient. If you are not the intended recipient, any copying, forwarding, disclosure, distribution or use of any part of this message or its attachments is strictly prohibited and may be unlawful. If you believe that you received this email in error, please do not read it or any attachments thereto, and notify the sender immediately by email and then delete this message from your system. Thank you.

Lindsay_Davies

Hi Greg,

My query seems to confirm that there is one distinct nodeID per day, but there could still be duplicate nodeids across different days.

Let me extend that further…

If you group by nodeid then your calendar date, you should see items with count > 1.

Select ClassificationID, Nodeid, left(dateapplied,11) as Date, count(*)
from ADVClassApplied adv with (NOLOCK)
where DateApplied between '06-01-2016' and '07-01-2016' and ClassificationID in (106332120, 167084905, 288477278)
group by ClassificationID , NodeID , left(dateapplied,11)
HAVING count(*) > 1
Order By 1, 2, 4 desc

I have my fingers crossed.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES
D +44 (0)1923 299 378

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 09 June 2016 22:22
To: eLink Recipient
Subject: Re RE Multiple Group By with a Count

Re RE Multiple Group By with a Count

Posted by Kellogg, Greg On 06/09/2016 05:22 PM

Thanks Lindsay. When I run it as you posted it I get No Results Found.

Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200

the iQ Business Group

intelligence. applied.

www.IQBGinc.com

RE Multiple Group By with a Count

Posted by Davies, Lindsay On 06/09/2016 04:01 PM

Hi Greg,

You are counting distinct nodeids WITHIN each reported date.

A nodeid may being reported on more than one line because it was classified on more than one date in the range.

If you want to see some detail of those, try this adapted query…

This will show you those nodeids which occur more than once.

Hope this helps explain things.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES
D +44 (0)1923 299 378

Multiple Group By with a Count

Posted by Kellogg, Greg On 06/09/2016 10:32 AM

I run the following SQL and get the following results:

 106332120   24245

 167084905     632

 288477278    5446

I add my Date field and run the adjusted SQL and get the following results:

 106332120   Jun 3 2016    1663

 106332120   Jun 7 2016   22915

 106332120   Jun 8 2016     250

 167084905   Jun 3 2016    50

 167084905   Jun 7 2016   599

 167084905   Jun 8 2016     5

 288477278   Jun 7 2016   5353

 288477278   Jun 8 2016     93

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Multiple Group By with a Count
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Multiple Group By with a Count
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Greg Kellogg

Just thought I would provide an update. One of my co-workers suggested using a cte query first and then joining to that query to limit the query to only one instance of the same NodeID. Here is the query we used:

WITH UniqueNodeIDs (NodeID)
AS
(SELECT DISTINCT NodeID
FROM AdvClassApplied
GROUP BY NodeID
)

SELECT left(dateapplied,11) as Date,
case
when providertype = 'GMJA000' then 'BLM'
when providertype = 'GMJA001' then 'BIA'
when providertype = 'GMJA002' then 'BOEM'
when providertype = 'GMJA003' then 'BOEM'
when providertype = 'GMJA004' then 'BSEE'
when providertype = 'GMJA005' then 'IOS'
when providertype = 'GMJA006' then 'IOS'
when providertype = 'GMJA007' then 'IBC'
when providertype = 'GMJA008' then 'FWS'
when providertype = 'GMJA009' then 'IOS'
when providertype = 'GMJA010' then 'IBC'
when providertype = 'GMJA011' then 'NPS'
when providertype = 'GMJA012' then 'OHA'
when providertype = 'GMJA013' then 'OIG'
when providertype = 'GMJA014' then 'ONRR'
when providertype = 'GMJA015' then 'OSM'
when providertype = 'GMJA016' then 'OST'
when providertype = 'GMJA017' then 'Other'
when providertype = 'GMJA018' then 'SOL'
when providertype = 'GMJA019' then 'USBR'
when providertype = 'GMJA020' then 'USGS'
else 'Other'
end
as Bureau, dt.Name as 'Classification Name', COUNT(A.NodeID) as Number
FROM AdvClassApplied A
       JOIN UniqueNodeIDs U ON (A.NodeID = U.NodeID)
       JOIN DTree dt on (A.ClassificationID = dt.DataID)
       JOIN DVersData dv on (A.NodeID = dv.DocID)
       JOIN ProviderData pd on (dv.ProviderId = pd.providerID)

WHERE DateApplied between '01-01-2016' and '06-01-2016'
GROUP BY left(dateapplied,11), A.ClassificationID, A.SubType, dt.Name, providertype
ORDER BY 1, 2, 3

Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200

the iQ Business Group

intelligence. applied.

www.IQBGinc.com

From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>
Sent: Thursday, June 9, 2016 9:45:04 PM
To: eLink Recipient
Subject: RE Re RE Multiple Group By with a Count

RE Re RE Multiple Group By with a Count

Posted by Davies, Lindsay On 06/09/2016 10:44 PM

Hi Greg,My query seems to confirm that there is one distinct nodeID per day, but there could still be duplicate nodeids across different days.Let me extend that furtherIf you group by nodeid then your calendar date, you should see items with count > 1.Select ClassificationID, Nodeid, left(dateapplied,11) as Date, count(*)
from ADVClassApplied adv with (NOLOCK)
where DateApplied between '06-01-2016' and '07-01-2016' and ClassificationID in (106332120, 167084905, 288477278)
group by ClassificationID , NodeID , left(dateapplied,11)
HAVING count(*) > 1
Order By 1, 2, 4 descI have my fingers crossed.

RegardsLindsay Lindsay Davies
Information Systems Administrator
RES
D +44 (0)1923 299 378

Re RE Multiple Group By with a Count

Posted by Kellogg, Greg On 06/09/2016 05:22 PM

Thanks Lindsay. When I run it as you posted it I get No Results Found.

Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200

the iQ Business Group

intelligence. applied.

www.IQBGinc.com

RE Multiple Group By with a Count

Posted by Davies, Lindsay On 06/09/2016 04:01 PM

Hi Greg,

You are counting distinct nodeids WITHIN each reported date.

A nodeid may being reported on more than one line because it was classified on more than one date in the range.

If you want to see some detail of those, try this adapted query

This will show you those nodeids which occur more than once.

Hope this helps explain things.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES
D +44 (0)1923 299 378

Multiple Group By with a Count

Posted by Kellogg, Greg On 06/09/2016 10:32 AM

I run the following SQL and get the following results:

 106332120   24245

 167084905     632

 288477278    5446

I add my Date field and run the adjusted SQL and get the following results:

 106332120   Jun 3 2016    1663

 106332120   Jun 7 2016   22915

 106332120   Jun 8 2016     250

 167084905   Jun 3 2016    50

 167084905   Jun 7 2016   599

 167084905   Jun 8 2016     5

 288477278   Jun 7 2016   5353

 288477278   Jun 8 2016     93

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Multiple Group By with a Count
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Multiple Group By with a Count
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Multiple Group By with a Count
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center