SQLite multiple count columns

Sam K
Sam K used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
Most Valuable Expert 2011
Commented:
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.

Author

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 Officer
Most Valuable Expert 2011

Commented:
You are most welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial