Home » SQL & PL/SQL » SQL & PL/SQL » count per group (web intelligence)
count per group [message #680676] Sun, 31 May 2020 10:37 Go to next message
jtylerrand
Messages: 3
Registered: May 2020
Junior Member
I am working in Web Intelligence Business Objects and it uses Oracle PL/SQL. I have no ability to create temp tables, so I need to have a single SQL statement that will return the desired results.

The data has the following format:

Sector, Customer_ID, Fiscal Year, Datetime

Sample data:


SDC, 1234, 2030, 3/21/2020
SDC, 456, 2020, 3/23/2020
ELC, 1234, 2020, 3/24/2020
SDC, 1234, 2020, 3/28/2020


I need to identify the count of customer_IDs per sector where that returned anywhere after their initial visit to each sector.

So in the above example, customer_ID 1234 would be credited to Sector SDC since he had a transaction after his initial. Customer_ID 456 would not be counted for ELC since he did not have a subsequent transaction after their initial.

Customer_ID 1234 would also be credited to Sector ELC because he returned after his visit to ELC. It doesn't matter where the follow-up visit is made, only that a follow-up visit occurs within the time frame.

The important part is that every sector where the customer_ID shows up gets credit for them only if they return anywhere afterwards.

I imagine it would look something like this:




Select R.Sector, R.customer_ID, count(R.Return)
From
(
(Select fiscal year, customer_ID, count(ROWNUM) as Returns
From Table
GROUP BY customer_ID, fiscal year
Having count(rownum)>1) T,
(Select min(row num) as min_app, customer_ID, sector
From Table
Group by sector, customer_ID) T1
Where T.customer_ID=T1.customer_ID
) R
Group by R.Sector, R.customer_ID
Where T.row num> T1.min_app


I can identify the customers who return more than once to the same sector, but I can't seem to also get the customers that return to a different sectors, but give the first sector the credit for that customer.

Hopefully my explanation is thorough enough for the geniouses of this forum to help. Feel free to ask any additional questions. Thanks for your time.
Tyler
Re: count per group [message #680677 is a reply to message #680676] Sun, 31 May 2020 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Sun, 31 May 2020 11:34]

Report message to a moderator

Re: count per group [message #680678 is a reply to message #680676] Sun, 31 May 2020 13:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
WITH SAMPLE(SECTOR, CUSTOMER_ID, FISCALYEAR, DATETIME)
  AS (
      SELECT 'SDC',1234,2020,DATE '2020-3-21' FROM DUAL UNION ALL
      SELECT 'SDC',456,2020,DATE '2020-3-23' FROM DUAL UNION ALL
      SELECT 'ELC',1234,2020,DATE '2020-3-24' FROM DUAL UNION ALL
      SELECT 'SDC',1234,2020,DATE '2020-3-28' FROM DUAL
     ),
T AS (
      SELECT  SECTOR,
              CASE
                WHEN ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY DATETIME) > 1 THEN NULL
                WHEN COUNT(*) OVER(PARTITION BY CUSTOMER_ID) = 1 THEN NULL
                ELSE CUSTOMER_ID
              END CUSTOMER_ID
        FROM  SAMPLE
     )
SELECT  SECTOR,
        COUNT(CUSTOMER_ID) CNT
  FROM  T
  GROUP BY SECTOR
  ORDER BY SECTOR
/

SECTOR        CNT
------ ----------
ELC             0
SDC             1

SQL>
SY.
Re: count per group [message #680680 is a reply to message #680678] Sun, 31 May 2020 18:47 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Ah, I missed "Customer_ID 1234 would also be credited to Sector ELC because he returned after his visit to ELC":

WITH SAMPLE(SECTOR, CUSTOMER_ID, FISCALYEAR, DATETIME)
  AS (
      SELECT 'SDC',1234,2020,DATE '2020-3-21' FROM DUAL UNION ALL
      SELECT 'SDC',456,2020,DATE '2020-3-23' FROM DUAL UNION ALL
      SELECT 'ELC',1234,2020,DATE '2020-3-24' FROM DUAL UNION ALL
      SELECT 'SDC',1234,2020,DATE '2020-3-28' FROM DUAL
     ),
T AS (
      SELECT  SECTOR,
              CASE
                WHEN ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY DATETIME) = COUNT(*) OVER(PARTITION BY CUSTOMER_ID) THEN NULL
                ELSE CUSTOMER_ID
              END CUSTOMER_ID
        FROM  SAMPLE
     )
SELECT  SECTOR,
        COUNT(CUSTOMER_ID) CNT
  FROM  T
  GROUP BY SECTOR
  ORDER BY SECTOR
/

SECTOR        CNT
------ ----------
ELC             1
SDC             1

SQL>
SY.
Re: count per group [message #680681 is a reply to message #680680] Sun, 31 May 2020 18:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Also, it isn't clear if you want count of customers or count of distinct customers. If later, use:

WITH SAMPLE(SECTOR, CUSTOMER_ID, FISCALYEAR, DATETIME)
  AS (
      SELECT 'SDC',1234,2020,DATE '2020-3-21' FROM DUAL UNION ALL
      SELECT 'SDC',456,2020,DATE '2020-3-23' FROM DUAL UNION ALL
      SELECT 'ELC',1234,2020,DATE '2020-3-24' FROM DUAL UNION ALL
      SELECT 'SDC',1234,2020,DATE '2020-3-28' FROM DUAL
     ),
T AS (
      SELECT  SECTOR,
              CASE
                WHEN ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY DATETIME) = COUNT(*) OVER(PARTITION BY CUSTOMER_ID) THEN NULL
                ELSE CUSTOMER_ID
              END CUSTOMER_ID
        FROM  SAMPLE
     )
SELECT  SECTOR,
        COUNT(DISTINCT CUSTOMER_ID) CNT
  FROM  T
  GROUP BY SECTOR
  ORDER BY SECTOR
/
SY.
Re: count per group [message #680692 is a reply to message #680681] Mon, 01 June 2020 09:54 Go to previous messageGo to next message
jtylerrand
Messages: 3
Registered: May 2020
Junior Member
Solomon,

First off I wanted to thank you for your response, I greatly appreciate the help.

I need the distinct count of Customers per Sector with at least one return after visiting that Sector.

A Sector gets credit for a customer only once.

So if the Data were


SELECT 'SDC',1234,2020,DATE '2020-3-21' FROM DUAL UNION ALL
SELECT 'SDC',1234,2020,DATE '2020-3-23' FROM DUAL UNION ALL
SELECT 'ELC',1234,2020,DATE '2020-3-24' FROM DUAL UNION ALL
SELECT 'ELC',1234,2020,DATE '2020-3-25' FROM DUAL UNION ALL
SELECT 'SDC',1234,2020,DATE '2020-3-28' FROM DUAL

The Results should be:

Sector Customers CNT
SDC 1
ELC 1

It doesn't matter that the Customer went to Sector SDC 3 times total and ELC 2 Times.

What matters is after going to SDC on 3/21/2020, they had another visit to any Sector. (This is what should give SDC the Credit for his customer_ID

The Visit to Sector ELC on 3/24/2020, and then any visit after that, is what should give ELC credit for Customer_ID 1234 as well.

I hope this helps to clear up my requirements.


I took your code and found that I needed to add a "COUNT DISTINCT" in order to prevent SDC and ELC from getting credit more than once for a single customer.

WITH SAMPLE(SECTOR, CUSTOMER_ID, FISCALYEAR, DATETIME)
AS (
SELECT 'SDC',1234,2020, '2020-3-21' UNION ALL
SELECT 'SDC',1234,2020, '2020-3-23' UNION ALL
SELECT 'ELC',1234,2020, '2020-3-24' UNION ALL
SELECT 'ELC',1234,2020, '2020-3-25' UNION ALL
SELECT 'SDC',1234,2020, '2020-3-28'
)


SELECT T.SECTOR,
COUNT(DISTINCT T.CUSTOMER_ID) AS CNT
FROM (

SELECT SECTOR,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY DATETIME) = COUNT(*) OVER(PARTITION BY CUSTOMER_ID) THEN NULL
ELSE CUSTOMER_ID
END CUSTOMER_ID

FROM SAMPLE
) T
GROUP BY SECTOR
ORDER BY SECTOR

SECTOR CNT
ELC 1
SDC 1

This is an ingenious piece of code. I have use the Row_Number Function before, but never in this way.

Thank you so much for your help!!

Sincerely,
Tyler


Re: count per group [message #680693 is a reply to message #680692] Mon, 01 June 2020 10:00 Go to previous messageGo to next message
jtylerrand
Messages: 3
Registered: May 2020
Junior Member
I almost hate to ask, but now that I have the Count Per Sector, can we break down the demographics of customers that are return customers? I have additional fields about the customers that I would like to be able to have the totals for in addition to the overall Total. For instance I know the age of customers. Could I add that field to the Select Statement and group by it to break down the overall total by their ages as well? I still need to assign customers to the sector they visited first, and then returned anywhere, but knowing the age breakdown of those customers will help me tremendously. Thanks again.
Re: count per group [message #680694 is a reply to message #680692] Mon, 01 June 2020 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please FORMAT your post as requested.
Read How to use [code] tags and make your code easier to read.

Re: count per group [message #680695 is a reply to message #680693] Mon, 01 June 2020 11:17 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Sure you can. Please provide data sample (create table + insert statements) and desired results along with logic to get them.

SY.
Previous Topic: ORA-06530: Reference to uninitialized composite
Next Topic: Error ORA-00932 when viewing data from an OLAP cube
Goto Forum:
  


Current Time: Thu Mar 28 09:29:21 CDT 2024