asked on

Select count left join

Hello,

I am asking for your help to help me correct this little bit of code.
In my tb_user table, I have the geographical location of the member as well as his identifier.
In the table tb_jeu_recap, I have the identifier and the date of its participations.

I would like to have the number of user being all in the same geographic area in a time interval.

$jan_b2 = $pdo->query("SELECT 
COUNT(R.NIC_Handle) AS NIC_Handle
,R.Date

,U.NIC_Handle
,U.Pays
,U.Ville

FROM tb_jeu_recap R
LEFT JOIN tb_user U ON U.NIC_Handle = R.NIC_Handle
WHERE R.Date BETWEEN '2020-01-01' AND '2020-01-31' AND U.Ville = 'Abidjan'

GROUP BY R.NIC_Handle, R.Date, U.Pays, U.Ville
")->fetchColumn();

echo $jan_b2;

Open in new window

Value is always 1

Can you help me please ?
thank you in advance

ste5an

Wrong JOIN direction and the date interval must be a JOIN predicate and remove the date from the GROUP BY clause:

SELECT U.NIC_Handle,
       U.Pays,
       U.Ville,
       COUNT(R.NIC_Handle) AS Cnt,
       MIN(R.Date) AS MinDate,
       MAX(R.Date) AS MaxDate
FROM tb_user U
    LEFT JOIN tb_jeu_recap R
        ON U.NIC_Handle = R.NIC_Handle
           AND R.Date
           BETWEEN '2020-01-01' AND '2020-01-31'
WHERE U.Ville = 'Abidjan'
GROUP BY U.NIC_Handle,
         U.Pays,
         U.Ville;

Open in new window

Jaber Ahmad

ASKER

Thank you Ste5an for taking an interest in my subject.
When I count the number of the table tb_jeu_recap in the interval of the date, I am at 3376

Now your code returns me 42619 ...
Isn't there a problem somewhere?

ste5an

Well, whithout concise and complete example.. e.g. T-SQL

DECLARE @tb_user TABLE
(
    NIC_Handle INT,
    Pays INT,
    Ville NVARCHAR(255)
);

INSERT INTO @tb_user
(
    NIC_Handle,
    Pays,
    Ville
)
VALUES
(1, 1, 'London'),
(2, 1, 'Berlin'),
(3, 1, 'Tokyo'),
(4, 1, 'Abidjan');

DECLARE @tb_jeu_recap TABLE
(
    NIC_Handle INT,
    [Date] DATE
);

INSERT INTO @tb_jeu_recap
(
    NIC_Handle,
    Date
)
VALUES
(2, '2020-01-11'),
(2, '2020-01-31'),
(2, '2020-02-01'),
(2, '2020-03-01'),
(3, '2020-01-13'),
(3, '2020-02-01'),
(3, '2020-03-01'),
(4, '2020-01-01'),
(4, '2020-01-02'),
(4, '2020-01-03'),
(4, '2020-01-04');

SELECT U.NIC_Handle,
       U.Pays,
       U.Ville,
       COUNT(R.NIC_Handle) AS Cnt,
       MIN(R.Date) AS MinDate,
       MAX(R.Date) AS MaxDate
FROM @tb_user U
    LEFT JOIN @tb_jeu_recap R
        ON U.NIC_Handle = R.NIC_Handle
           AND R.Date
           BETWEEN '2020-01-01' AND '2020-01-31'
--WHERE U.Ville = 'Abidjan'
GROUP BY U.NIC_Handle,
         U.Pays,
         U.Ville;

Open in new window

returns

So I guess it's your data model and the actual data.

Jaber Ahmad

ASKER

Thank you for your responsiveness. I'm sorry, I always forget to post the details.

The expected result here is 8 because it will only retrieve the identifiers with the city Abidjan in the date range.

This query returns me 3376

$jan_b1 = $pdo->query("
SELECT 
COUNT(NIC_Handle) 
FROM tb_jeu_recap 
WHERE Date BETWEEN '2020-01-01' AND '2020-01-31'
")->fetchColumn();

Open in new window

Now if I filter with yours, I get 42619

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial