CODEWITHSUNDEEP
phpmysqlsqlcountsum
user3838158
user3838158

Reputation: 1

?Useing COUNT from Table and SUM from anther Table with related data

Table #1 Name: users

> ID  |  NAME  |  offerID  |  paymentDate
  1   |  user1 |  1        |  2014-07-14
  2   |  user2 |  2        |  2014-07-14
  3   |  user3 |  2        |  2014-07-30
  4   |  user4 |  1        |  2014-07-14
  5   |  user5 |  3        |  2014-07-14
  6   |  user6 |  1        |  2014-07-30

Table #2 Name: offer

> ID | NAME  |  PRICE
  1  | offer1|  25
  2  | offer2|  45
  3  | offer3|  75

if you see i have (3) users in offer1 (2)users in offer2 (1)users in offer3

how i can count how many user will pay me in '2014-07-14' and how much?,

i need result will be for '2014-07-14' Like this

> paymentDate  |  usersCount  |  Totalprice
  2014-07-14   |  4           |  175

Upvotes: 0

Views: 38

Answers (4)

T McKeown
T McKeown

Reputation: 12857

SELECT U.PAYMENTDATE, COUNT(*) AS UserCount, SUM( O.PRICE) 
FROM USERS AS U
JOIN OFFERS AS O
  ON O.ID = U.OfferId
WHERE U.PAYMENTDATE = '2014-07-14'
GROUP BY U.PAYMENTDATE

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

How about this?

select u.paymentDate, count(*), sum(o.price)
from users u join
     offers o
     on u.offerId = o.id
where u.date = '2014-07-14'
group by u.paymentDate;

Of course, this doesn't say that they will pay. Only that the data says they will.

You can remove the where clause to get results for all dates.

Upvotes: 1

Siyual
Siyual

Reputation: 16917

Select  PaymentDate, Count(Distinct U.Id) usersCount, Sum(O.Price) Totalprice
From    Users U
Join    Offer O On O.Id = U.OfferId
Where   U.PaymentDate = '2014-07-14'
Group By U.PaymentDate

Upvotes: 0

Kai
Kai

Reputation: 3823

SELECT sum(offer.price) as Totalprice, count(users.id), paymentDate 
FROM users JOIN offers ON users.offerID = offers.ID  
WHERE paymentDate = '2014-07-14'
GROUP BY paymentDate;

Upvotes: 1

Related Questions