Count two different columns values from the same table using SQLite Database table in android

Question

I am trying to select multiple values from the same table. I need to select count values of the column rfid and process_status from the posts table and station_title from stations table.

Here are the two tables:

Posts table :

Id   ownerId   rfid    stationId      stationType    process_status
1       107        rfid1  raj1222681607   like             pending
2       107        rfid1  raj1222681607   like             pending
3      107        rfid1  raj1125396157    like             pending
4       107        rfid1  raj1222681607   like             
5       107        rfid2  raj1222681607   like             pending
6       107        rfid3  raj1222681607   like

Stations table :

Id   title    ownerId    stationId     stationLike    stationPic
1    Check-in one         107             raj1125396157    1              0
2    nfc station 01      107             raj1222681607     1             0

From these two tables I want to fetch data as

Total RFIDs : 5
Total Pending : 3
Station Title : nfc station 01

The where clause conditions are : ownerId = 107 and the stationId = 'raj1222681607' and the process_status = 'pending'

So far I can achieve the total rfids, station title value; but i am not able to get the total pending value counting the process status.

My query snippet:

  SELECT
        COUNT(p.rfid) as TotalTap,
        COUNT(p.process_status) as TotalPending,
        s.title
    FROM posts p
    inner join
    stations s
    on p.stationId = s.stationId
    WHERE
    p.ownerId = 107 AND p.stationId = 'raj1222681607'
    AND p.process_status = 'pending';

But this is giving the wrong output as : Total RFIDs : 3 (THIS IS WRONG!!) Total Pending : 3 Station Title : nfc station 01

CL. · Accepted Answer · 2015-02-24 15:01:19Z

1

To compute how many rows match a condition, use SUM over a boolean expression:

SELECT COUNT(*) AS TotalTap,
       SUM(process_status = 'pending') AS TotalPending
FROM Posts
WHERE ownerId = 107
  AND stationId = 'raj1222681607';

It does not really make sense to try to compute the third value in the same query; just use a separate, much simpler query:

SELECT title
FROM Stations
WHERE stationId = 'raj1222681607';

Android has a helper function for this:

String title = DatabaseUtils.stringForQuery(db,
                   "SELECT title FROM Stations WHERE stationId = ?",
                   new String[]{ "raj1222681607" });

answered Feb 24, 2015 at 15:01

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Happy Ninja Over a year ago

Hi CL, thanks for the correction. The process status value is string format. so sum will be the good option? i need to fetch all these information from one db function calling. so i need to also get the title value.

CL. Over a year ago

The value of the expression process_status = 'pending' is either 0 or 1.

Happy Ninja Over a year ago

my query is : SELECT COUNT(p.rfid) as TotalTap, (SELECT COUNT(p.process_status) FROM posts p WHERE p.ownerId = ? AND p.stationId = ? AND p.process_status = ?) as TotalPending, s.title FROM posts p inner join stations s on p.stationId = s.stationId WHERE p.ownerId = 107 AND p.stationId = 'raj1222681607' and p.process_status = 'pending'; but i can't get these values from the cursor call.

CL. Over a year ago

That is a different query.

Collectives™ on Stack Overflow

Count two different columns values from the same table using SQLite Database table in android

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related