CODEWITHSUNDEEP
mysql
Chama
Chama

Reputation: 199

Counting rows with a condition

i work with a table "ships" like this:

shipid
container1
container2

shipid and container1 are always filled, container2 is optional.

This is my query for now:

SELECT COUNT(container1) FROM table

How can i achieve that this query counts the amount of the container2's too?!

It must be a joined query made of those both queries:

SELECT COUNT(container1) FROM table
SELECT COUNT(container2) FROM table WHERE container2!=''

Thanks for any hints!

Upvotes: 0

Views: 51

Answers (3)

isaace
isaace

Reputation: 3429

To add to the previous answers, you can do this to get the count in 1 result. 

 SELECT SUM(count) FROM
  (
   SELECT COUNT(container1) as count FROM table
   UNION ALL
   SELECT COUNT(container2) as count FROM table
  ) as a

or you can do this:

SELECT COUNT(*) FROM
  (
   SELECT container1  FROM table
   UNION ALL
   SELECT container2  FROM table
  ) as a

Upvotes: 0

Fleury26
Fleury26

Reputation: 585

You can use SUM

SELECT 
     SUM(container1) AS c1
    ,SUM(CASE WHEN container2 IS NOT NULL THEN 1 ELSE 0 END) AS c2
FROM ships

Upvotes: 1

Bora Erbasoglu
Bora Erbasoglu

Reputation: 107

SELECT COUNT(container1) FROM table
UNION
SELECT COUNT(container2) FROM table

Upvotes: 1

Related Questions