PHP MySQL Count Total Issue

Question

I'm almost done with a project and this is my last issue.

I have a table where I had to use GROUP_CONCAT like this:

SELECT
    GROUP_CONCAT(DISTINCT nextgenorder_companyname) AS nextgenorder_companyname,
    GROUP_CONCAT(DISTINCT nextgenorder_company_entity) AS nextgenorder_company_entity,
    GROUP_CONCAT(DISTINCT nextgenorder_ordernumber) AS nextgenorder_ordernumber,
    GROUP_CONCAT(DISTINCT nextgenorder_deliverydate) AS nextgenorder_deliverydate
FROM nextgenorders2
WHERE nextgenorder_deliverydate='2020-11-11'
GROUP BY SUBSTRING_INDEX(REPLACE(nextgenorder_companyname, '/', ' '), ' ',1)
ORDER BY SUBSTRING_INDEX(REPLACE(nextgenorder_companyname, '/', ' '), ' ',1)

SQL Fiddle Example

Now I just need to get the total amount of companies on this table:

For example, the total for the table above would be 3. 3 Total companies.

I tried this:

SELECT 
count(DISTINCT nextgenorder_company_entity) as company, 
nextgenorder_deliverydate as ShipDate 
FROM nextgenorders2 
WHERE nextgenorder_deliverydate='2020-11-11' 
    GROUP BY nextgenorder_deliverydate

SQL Fiddle Example

But the total I get is 6. I just got introduce to GROUP_CONCAT and i know it's needed but I'm getting stuck. I also tried this:

SELECT GROUP_CONCAT(nextgenorder_companyname) AS nextgenorder_companyname,
GROUP_CONCAT(nextgenorder_deliverydate) nextgenorder_deliverydate
  FROM
(
  SELECT COUNT(*) Total
    FROM nextgenorders2
  WHERE nextgenorder_deliverydate='2020-11-11'
 GROUP BY SUBSTRING_INDEX(REPLACE(nextgenorder_companyname, '/', ' '), ' ',1)

) q

Gordon Linoff · Accepted Answer · 2020-02-24 01:48:10Z

1

You have an expression to extract the "company name" from the column in the database.

Use that expression for the count(distinct):

SELECT count(DISTINCT SUBSTRING_INDEX(REPLACE(nextgenorder_companyname, '/', ' '), ' ',1)
            ) as company, 
       nextgenorder_deliverydate as ShipDate 
FROM nextgenorders2 
WHERE nextgenorder_deliverydate = '2020-11-11' 
GROUP BY nextgenorder_deliverydate;

Here is a db<>fiddle.

answered Feb 24, 2020 at 1:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP MySQL Count Total Issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related