SQLite multiple count columns

Using SQLite how can I get multiple count columns in an SQL query?
I have a table that has two columns in the same table that I am querying, "workpack group" and "pull status". the pull status is either "Completed", Started" or "" ("" not, NULL, I have inherited this table from a previous person who did not normalize the tables)
I would like to get a count of each pull status for each workpack group. I have achieved this by creating 3 separate views, querying the views and then deleting the views. This is a very wasteful method, that I would like to avoid if possible.
Below is my working code, as an example and i have attached a screenshot of the working output.
is there a better way to do this?
Thank you

CREATE VIEW wk_group_pulled AS SELECT
      cableList."Workpack Group",
      count(*)
FROM
      cableList
WHERE
      cableList."Workpack Group" != "A/G"
AND cableList."Pull Status" = "Completed"
GROUP BY
      cableList."Workpack Group";

CREATE VIEW wk_group_started AS SELECT
      cableList."Workpack Group",
      count(*)
FROM
      cableList
WHERE
      cableList."Workpack Group" != "A/G"
AND cableList."Pull Status" = "Started"
GROUP BY
      cableList."Workpack Group";

CREATE VIEW wk_group_blank AS SELECT
      cableList."Workpack Group",
      count(*)
FROM
      cableList
WHERE
      cableList."Workpack Group" != "A/G"
AND cableList."Pull Status" = ""
GROUP BY
      cableList."Workpack Group";

SELECT
      cableList."Workpack Group",
      wk_group_pulled."count(*)" AS completed,
      wk_group_started."count(*)" AS started,
      wk_group_blank."count(*)" AS pending
FROM
      cableList
LEFT JOIN wk_group_pulled ON cableList."Workpack Group" = wk_group_pulled."Workpack Group"
LEFT JOIN wk_group_started ON cableList."Workpack Group" = wk_group_started."Workpack Group"
LEFT JOIN wk_group_blank ON cableList."Workpack Group" = wk_group_blank."Workpack Group"
GROUP BY
      cableList."Workpack Group"
ORDER BY
      cableList."Workpack Group" ASC;

DROP VIEW
IF EXISTS wk_group_pulled;

DROP VIEW
IF EXISTS wk_group_started;

DROP VIEW
IF EXISTS wk_group_blank;
workpack_group_screen1.png
Sam KAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Hi.

You could use a conditional aggregate.  In other words, instead of including the filter in the WHERE clause, you would do it in each of the COUNT(...) functions within the SELECT.

Example:
COUNT(CASE WHEN cableList."Workpack Group" != "A/G" AND cableList."Pull Status" = "Completed" THEN 1 END)

Just copying and pasting from your sample SQL code, so please forgive any errors...just wanted to help you visualize the concept of conditional aggregate.

P.S. here is a reference: http://www.sqlite.org/lang_expr.html#case
By the way, my article A SQL Tidbit: Conditional Aggregates explains the concept further if you have further.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sam KAuthor Commented:
Thank you very much for your help and the supporting information, everything is working now. I have been trying to solve this issue for months, i knew it had to be something obvious.
Kevin CrossChief Technology OfficerCommented:
You are most welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.