CODEWITHSUNDEEP
mysqlsqldatabaseinner-join
BhavikKama
BhavikKama

Reputation: 8790

how to get sum of column value with inner join in one query?

i have one table name as a_ad_display in that i have following table

advId | Displaytime

I have One a_advertise table with following collumn

advertiseId| advertisename| mediaTypeId

I have one a_mediatype table with following collumn

mediaTypeId | medianame

1 | Image
2 | Animation
3 | video

there is multiple entry of advId in the a_display table

i want to fetch the sum of adv display with their mediaType like how much video ,image and animation type of advertise in a_display table

i have done this way 

SELECT 
  mdtype.medianame AS _type,
  SUM(mdtype.medianame = 'Image') AS _display 
FROM
  a_ad_display a 
  INNER JOIN a_advertise adv 
    ON adv.advertiseId = a.advId 
  INNER JOIN a_mediatype mdtype 
    ON mdtype.mediaId = adv.mediaTypeId 
GROUP BY medianame ;

but i got following results

enter image description here

but if write just sum(distinct mdtype.medianame) then its givng me error...so how can i get all the values of image video and animation in just one query?

Upvotes: 1

Views: 4321

Answers (4)

user2001117
user2001117

Reputation: 3777

Try this :

SELECT
    m.medianame,
    COUNT(as.advId) as Total
FROM a_mediatype as m
INNER JOIN a_advertise as a 
ON a.mediaTypeId = m.mediaTypeId
INNER JOIN  a_ad_display as ad 
ON ad.advId = a.advertiseId
GROUP BY m.mediaTypeId

Upvotes: 1

peterm
peterm

Reputation: 92785

You can try this

SELECT m.mediaTypeId, 
       MIN(m.medianame) AS `type`, 
       COUNT(m.mediaTypeId) AS `count`
  FROM a_ad_display a LEFT JOIN 
       a_advertise d on d.advertiseId=a.advId LEFT JOIN 
       a_mediatype m on m.mediaId=d.mediaTypeId 
GROUP BY m.mediaTypeId

Upvotes: 1

michelle.ann.diaz
michelle.ann.diaz

Reputation: 67

Try this...

SELECT mdtype.medianame as _type, SUM(mdtype.medianame) as _display
FROM a_ad_display a
INNER JOIN a_advertise adv ON ad.advertiseID=a.advId
INNER JOIN a_mediatype mdtype ON mdtype.mediaID=adv.mediaTypeId
GROUP BY _type
HAVING DISTINCT(_type)

Upvotes: 1

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT
    m.medianame,
    COUNT(as.advId) as Total
FROM    a_mediatype as m
INNER JOIN a_advertise as a ON a.mediaTypeId = m.mediaTypeId
INNER JOIN  a_ad_display as ad ON ad.advId = a.advertiseId
GROUP BY m.mediaTypeId

Upvotes: 3

Related Questions