CODEWITHSUNDEEP
mysql
ggupta
ggupta

Reputation: 707

Get count of one column from a table and count of same column matching in other table

I have two table as below

    table 1: raw
    dateCol id
1   01.01.2001 00:00:00 AAPL
2   02.01.2001 00:00:00 MSFT
3   03.01.2001 00:00:00 INFY

    table 2: universe
    udateCol    uid
1   01.01.2001 00:00:00 AAPL
2   02.01.2001 00:00:00 GOOGL
3   03.01.2001 00:00:00 INFY

i want to extract the count of ids from raw and count of ids from raw matching with uids in universe. So i tried the below query in Mysql:

select universe.udateCol, count(raw.id) as rawCount, count(universe.uid) as uniCount from universe left join raw on universe.udateCol = raw.dateCol AND raw.id = universe.uid group by universe.udateCol order by universe.udateCol;

using the above query, i'm getting the following output

    udateCol    rawCount    uniCount
1   01.01.2001 00:00:00 1   1
2   02.01.2001 00:00:00 0   1
3   03.01.2001 00:00:00 1   1

But i want the output to be like:

    udateCol    rawCount    uniCount
1   01.01.2001 00:00:00 1   1
2   02.01.2001 00:00:00 1   0
3   03.01.2001 00:00:00 1   1

If any one want to check live, then can check here

Edit1:

i want to get the count which exist in my raw table for each date, and count of those which are matching in universe table on the same date

Upvotes: 0

Views: 29

Answers (2)

cdaiga
cdaiga

Reputation: 4939

Try this:

SELECT DATE(A.dateCol) dateCol, COUNT(A.id) rawCount, COUNT(B.id) uniCount
FROM raw A LEFT JOIN universe B
ON A.id=B.uid AND DATE(A.dateCol)=DATE(B.udateCol)
GROUP BY DATE(A.dateCol);

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

Not an answer. Too long for a comment, and a little tired of trying to draw blood from this stone...

Consider the following:

drop table if exists raw;
drop table if exists universe;

create table raw(dateCol date, id VARCHAR(20),PRIMARY KEY(datecol,id));
create table universe(udateCol date, uid VARCHAR(20),PRIMARY KEY(udatecol,uid));

insert into raw values
('20010101','AAPL'),
('20010102','MSFT'),
('20010103','INFY'),
('20010104','HOG');


insert into universe values
('20010101','AAPL'),
('20010102','GOOGL'),
('20010103','INFY'),
('20010105','HOG');

Note: I have made assumptions about the PRIMARY KEY in each case. If this is incorrect, please clarify.

Given this data set, what should the desired result look like? Think before you answer.

Upvotes: 0

Related Questions