CODEWITHSUNDEEP
sqlitecount
Manos C
Manos C

Reputation: 333

Combine two counts in SQLite

I have the following table ("Table") format

 A             B         C
"801331"    "5755270"   "0"
"1761861"   "10556391"  "1"
"1761861"   "10557381"  "33"
"1761861"   "11069131"  "33"
"801331"    "24348751"  "0"
"801331"    "77219852"  "0"
"1761861"   "557880972" "0"

And I would like to count and present two different quantities in one table grouped by column A.

The first is: 

SELECT A, COUNT(*) FROM Table GROUP BY A

The second one has one condition:

SELECT A, COUNT(*) FROM Table WHERE C != 0 GROUP BY A

I want to have the following result

    A      1st  2nd
"1761861"  "4"  "3"
"801331"   "3"  "0"

I tried a few answers from questions such as thisOne yet I could not make it happen as the result is one row.

I get it is pretty easy, yet i cannot make it work.

Is there a (simple) way to do it?

Upvotes: 0

Views: 147

Answers (1)

Diego
Diego

Reputation: 832

SELECT A, sum(1),  sum(case when C <> 0 then 1 else 0 end) FROM Table  GROUP BY A;
SELECT A, count(*),  count(case when C <> 0 then A else null end) FROM Table  GROUP BY A;

Upvotes: 1

Related Questions