MySQL-Inserting count into a table

Question

I am trying to create a new table that contains a count for a specific column. The column I want the count for contains different organization names. I created a new table that contains two columns one for the count and one for the organization names. I able to get the count to work, but I can't figure out how to insert into the table. This is what I have currently:

INSERT INTO ORG_countnumber (COUNT_number, OrgLBN)
SELECT 
    COUNT(*), OrgLBN FROM ORG_NPI_HumanName_Add_Phone GROUP BY OrgLBN
as COUNT_number,
OrgLBN
AS OrgLBN
FROM ORG_NPI_HumanName_Add_Phone;

Thanks in advance!

Why not just this: INSERT INTO ORG_countnumber (COUNT_number, OrgLBN) SELECT COUNT(*), OrgLBN FROM ORG_NPI_HumanName_Add_Phone GROUP BY OrgLBN — Hackerman
– Hackerman, Commented Jul 17, 2017 at 15:59

Tim Biegeleisen · Accepted Answer · 2017-07-17 16:01:22Z

1

I think the first half of your query was on the right track:

INSERT INTO ORG_countnumber (COUNT_number, OrgLBN)
SELECT COUNT(*), OrgLBN
FROM ORG_NPI_HumanName_Add_Phone
GROUP BY OrgLBN

But as was suggested in the comment, a view might make more sense here:

CREATE VIEW orgCountView AS
SELECT COUNT(*), OrgLBN
FROM ORG_NPI_HumanName_Add_Phone
GROUP BY OrgLBN

The reason a view would make sense is that the counts in your table could change all the time, and therefore the data in the ORG_countnumber table could easily become stale. A view would allow you to get the latest numbers without committing to a new table.

answered Jul 17, 2017 at 16:01

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

Juan Carlos Oropeza · Accepted Answer · 2017-07-17 16:00:18Z

0

You put the GROUP BY on the wrong place.

INSERT INTO ORG_countnumber (COUNT_number, OrgLBN)
SELECT 
    COUNT(*) as COUNT_number, OrgLBN
FROM ORG_NPI_HumanName_Add_Phone
GROUP BY OrgLBN;

answered Jul 17, 2017 at 16:00

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

MySQL-Inserting count into a table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related