yadi shahryary yadi shahryary - 3 months ago 10
SQL Question

How to join and select from tables with conditions?

I have 2 tables and wanna to join and get sum fields with aggression.

My tables are:

enter image description here

That "PriceListid" in tables are same ( join fields)

So, I want to find Users("UserID") and "Price" but with "Type=0(viewed) or 1(clicked) " and also get SUM("ShowQuantity"), Sum("ClickQuantity) and "Title","URL",...
The results should grouped by "UserID".

The results would be like this:

enter image description here

Answer

Based on the new information in your comments, the main table can be used to find which user viewed/clicked on which offer how many times:

select 
    pl.userid, 
    pl.PriceListId, 
    pl.offerid,
    sum(case when pl.type = 0 then 1 else 0 end) as 'times_viewed',
    sum(case when pl.type = 1 then 1 else 0 end) as 'times_clicked'
from PriceList pl
group by pl.userid, pl.PriceListId, pl.offerid
having sum(case when pl.type = 1 then 1 else 0 end) > 0

Then expanding this base query, you can join to the second table to get title and url:

select 
    pl.userid, 
    pl.pricelistid, 
    pl.offerid,
    sum(case when pl.type = 0 then 1 else 0 end) as 'times_viewed',
    sum(case when pl.type = 1 then 1 else 0 end) as 'times_clicked',
    max(pld.Title) as 'Title',
    max(pld.URL) as 'URL',
    max(pld.Model) as 'Name'
from PriceList pl
inner join PriceListDetails pld on pld.OfferId = pl.OfferId and pld.pricelistid = pl.pricelistid
group by pl.userid, pl.pricelistid, pl.offerid
having sum(case when pl.type = 1 then 1 else 0 end) > 0

To further filter this to only show records where PriceListId meets certain criteria as described in your comments, this should work:

select 
    pl.userid, 
    pl.pricelistid, 
    pl.offerid,
    sum(case when pl.type = 0 then 1 else 0 end) as 'times_viewed',
    sum(case when pl.type = 1 then 1 else 0 end) as 'times_clicked',
    max(pld.Title) as 'Title',
    max(pld.URL) as 'URL',
    max(pld.Model) as 'Name'
from PriceList pl
inner join PriceListDetails pld on pld.OfferId = pl.OfferId and pld.pricelistid = pl.pricelistid
where pl.pricelistid in (
    select articleid
    from c
    where c.state = 2
    and c.Created >= datediff(dd,0,getdate())
)
group by pl.userid, pl.pricelistid, pl.offerid
having sum(case when pl.type = 1 then 1 else 0 end) > 0