Drupal 10: Generate A List Of Published And Unpublished Node Counts

Use the following query to generate a list of the published and unpublished counts of nodes in your database.

SELECT
	type,
	SUM(case when status = 1 then 1 else 0 end) as publishedCount,
	SUM(case when status = 0 then 1 else 0 end) as unPublishedCount
FROM
	node_field_data
GROUP BY
	node_field_data.type
ORDER BY
	publishedCount DESC; 

Written using MySQL/MariaDB.