UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Count Of Elements By Day, latest    
 
   
kruuth
post Feb 24 2016, 04:05 PM
Post#1



Posts: 849
Joined: 30-May 07



I'm trying to get a query that returns a date, and then a process code, a reason code, then a count of serial numbers and customer numbers so it looks like this:

Date-process_code-reason_code-count_serial-count_cust_num
1/1/2016-RET-BAD-105-218
1/2/2016-RET-BAD-200-19
1/3/2016-RET-BAD-173-46


So far I have had no luck with this. The reason code is always the same, and the process code is also the same. I'm trying some sort of select distinct but I'm having issues with the count.
Go to the top of the page
 
GroverParkGeorge
post Feb 24 2016, 04:26 PM
Post#2


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


Please show us the SQL you have tried. "SELECT Distinct" won't be appropriate. You want a GROUP BY clause and use Count(*) for the count of records in each group.
Go to the top of the page
 
kruuth
post Feb 24 2016, 04:38 PM
Post#3



Posts: 849
Joined: 30-May 07



You're right. I made a few changes:

CODE
select to_date(from_unixtime(unix_timestamp(data_dt))) as data_dt,reason_code, process_code,count(serial_no) as Count_of_serial_number, count(cust_id) as Count_of_customer_number
from main.cust_ret_track
where process_code = "RET" and
reason_code = "BAD" and
(cust_id is not NULL or serial_no is not NULL)
group by data_dt, reason_code, process_code
Go to the top of the page
 
kruuth
post Feb 24 2016, 04:46 PM
Post#4



Posts: 849
Joined: 30-May 07



Forgot...that query seems to work, but the counts are the same
Go to the top of the page
 
GroverParkGeorge
post Feb 24 2016, 05:37 PM
Post#5


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


Does that mean you have a resolution then?
Go to the top of the page
 
LPurvis
post Feb 25 2016, 05:08 AM
Post#6


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

>> but the counts are the same
Suggests that you're expecting count(serial_no) and count(cust_id) to return different values?
What those two aggregate functions will do is return the count of non-Null entries in each field respectively. If they're both never Null, they'll both always return the same count (the same as the count of rows).

You may need to explain a bit more generally the results you're trying to extract, but it sounds like you want a count of the distinct values in each.
It seems you're using Oracle (so not my bag personally), however AFAIK, you should be fine using the COUNT DISTINCT method.
CODE
SELECT to_date(FROM_unixtime(unix_timestamp(data_dt))) As data_dt,reason_code, process_code, COUNT(DISTINCT serial_no) AS Count_of_serial_number, COUNT(DISTINCT cust_id) AS Count_of_customer_number
...


Any better?

Cheers
Go to the top of the page
 
kruuth
post Feb 25 2016, 08:02 AM
Post#7



Posts: 849
Joined: 30-May 07



It possible that there are going to be the same serial number and customer numbers, and they can show up repeatedly. That's kind of the problem. Could I do a union or something of two queries?
Go to the top of the page
 
LPurvis
post Feb 25 2016, 08:08 AM
Post#8


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Did you try the alternative SELECT statement I suggested?
Go to the top of the page
 
Jeff B.
post Feb 25 2016, 08:17 AM
Post#9


UtterAccess VIP
Posts: 9,882
Joined: 30-April 10
From: Pacific NorthWet


How depends on what -- what does your underlying data look like? That is, what table structure are you querying?
Go to the top of the page
 
kruuth
post Feb 25 2016, 08:58 AM
Post#10



Posts: 849
Joined: 30-May 07



It's a single table in this case
Go to the top of the page
 
LPurvis
post Feb 25 2016, 09:12 AM
Post#11


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Did you try the alternative SELECT statement I suggested?
Go to the top of the page
 
kruuth
post Feb 25 2016, 09:28 AM
Post#12



Posts: 849
Joined: 30-May 07



Just did it. Sorry I had to reinstall my tools. It's working but it's 1-off each day. Is it possible that there's a difference between is NOT NULL and <> "" ?
Go to the top of the page
 
LPurvis
post Feb 25 2016, 10:02 AM
Post#13


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


There is, of course, a difference between is NOT NULL and <> "" . Though not necessarily one that impacts your issue here.

I think your best way forward to ensure what you're seeing is to compare a day.
Check the values returned for one of the counts.
When you say "but it's 1-off each day", in which direction?
You know there's one more in the count than there should be? Or one too few?
Are you wanting Null to be included? It wouldn't seem so from the attempt to exclude it in "(cust_id is not NULL or serial_no is not NULL)"

COUNT and COUNT(DISTINCT both ignore Nulls on a column, but it seems it's not that Null you're missing, or might it be?

Cheers
Go to the top of the page
 
kruuth
post Feb 25 2016, 10:19 AM
Post#14



Posts: 849
Joined: 30-May 07



You're right...I'm trying to exclude nulls in this case...I completely forgot that it ignores nulls in a count. I'll check across multiple days and see what I get.
Go to the top of the page
 
LPurvis
post Feb 25 2016, 02:13 PM
Post#15


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


OK then.

Well, while I'm far from wild about is as a solution, you could use a replacement value to count.
Sorry, can't give you an Oracle version:

..., COUNT(DISTINCT COALESCE(serial_no, -1)) AS Count_of_serial_number, COUNT(DISTINCT COALESCE(cust_id, -1)) AS Count_of_customer_number

Or add on another count specifically for Nulls...
There are options. (Just less efficient ones.)

Cheers
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 03:56 PM