CODEWITHSUNDEEP
sqlsqlitejoin
edgarmtze
edgarmtze

Reputation: 25058

Counting distinct number of items returned by Joining tables

I have 3 tables, User, Payment, Type Of Payment like:

CREATE TABLE User (
  `userID` INTEGER PRIMARY KEY AUTOINCREMENT, 
  `userName` varchar(4)
  );
INSERT INTO User values    
    (1, 'Tom'),
    (2, 'Kate'),
    (3, 'Karla');

CREATE TABLE typePayment ( 
  typeID    INTEGER PRIMARY KEY AUTOINCREMENT, 
  namePayment      VARCHAR(6) NOT NULL
);
INSERT INTO typePayment values
(1,'Cash'),
(2,'Check'),
(3,'Both');

CREATE TABLE Payment (
  `paymentID` INTEGER PRIMARY KEY AUTOINCREMENT,
  `idUser` int, 
  `idTypePayment` int, 
  `info` varchar(14)  
  );

INSERT INTO Payment VALUES
    (1, 1, 1, 'ball'),
    (2, 1, 1, 'book'),
    (3, 1, 2, 'book'),
    (4, 2, 1, 'book');

When Joining tables to consult the payments made to guys I got

userID  userName    namePayment     info
1       Tom         Cash            ball
1       Tom         Cash            book
1       Tom         Check           book
2       Kate        Cash            book

By the following query

SELECT u.userID ,u.userName, te.namePayment , e.info
FROM User u
  INNER JOIN Payment e 
     ON u.userID = e.idUser     
  INNER JOIN typePayment te 
     ON e.idTypePayment = te.typeID
 order by u.userID;

However I want to count the number of total type Of Payments done to each guy like:

userID  userName    namePayment     info    noCash  noCheck  noBoth
1       Tom         Cash            ball        2         1       0
1       Tom         Cash            book        2         1       0
1       Tom         Check           book        2         1       0
2       Kate        Cash            book        1         0       0

How to do it? (please take a look at fiddle, I am using SQLite)

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Why would you want multiple rows for a user? Also, noBoth doesn't make sense.

This query gets the different types of payments per user:

SELECT u.userID, u.userName,
       SUM(CASE WHEN te.namePayment = 'Cash' THEN 1 ELSE 0 END) as NumCash,           
       SUM(CASE WHEN te.namePayment = 'Check' THEN 1 ELSE 0 END) as NumCheck,
       SUM(CASE WHEN te.namePayment IN ('Cash', 'Check' ) THEN 1 ELSE 0 END) as NumBoth
FROM User u INNER JOIN
     Payment e 
     ON u.userID = e.idUser INNER JOIN
     typePayment te 
     ON e.idTypePayment = te.typeID
GROUP BY u.userID;
ORDER BY u.userID;

If you really like, you can join this back to the more detailed information to add the columns per payment.

Upvotes: 3

Related Questions