2 tablo sonucunu birleştirme konusunda yardım

06-04-2015, 16:12:03

Dostlar 2 sorguyu birleştirmek istiyorum.

1. Sorgu şu şeklide

Alıntı

SELECT sites.country, DATE(sites .created_at) DateOnly, COUNT(sites.host_name) AS Downloads
FROM `sites`
WHERE sites.country = "TR"
GROUP BY MONTH(DateOnly)
ORDER BY DateOnly DESC
LIMIT 200

2. Sorgu da şu şekilde

Alıntı

SELECT `country`, DATE(`created_at`) DateOnly, count(country) as Premium FROM `transactions`
WHERE `type` = "premium" AND `payment` > 0 AND country = "Turkey"
GROUP BY MONTH(DateOnly)
ORDER BY DateOnly DESC
LIMIT 200

İki tablo birleşip, Country - DateOnly - Downloads - Premium şeklinde olmasını istiyorum. Bu konuda bana yardımcı olmak isteyen?

06-04-2015, 17:35:09

#2

rob33n

Kimlik doğrulama veya yönetimden onay bekliyor.

UNION, UNION ALL kullanarak yapabilirsin. Temp table kullanarakta yapabilirsin.

select country, GETDATE(), COUNT(host_name) as 'Downloads', '0' as 'Premium' from sites
union all
select country, GETDATE(), '0' as 'Downloads', COUNT(country) as 'Premium' from transactions

Temp table ile;

create table #tempTable
( 
Country nvarchar(250), DateOnly datetime, Downloads int, Premium int
)

insert into #tempTable (Country, DateOnly, Downloads, Premium)


select country, GETDATE(), COUNT(host_name) as 'Downloads', '0' as 'Premium' from sites
union all
select country, GETDATE(), '0' as 'Downloads', COUNT(country) as 'Premium' from transactions

select * from #tempTable
drop table #tempTable

07-04-2015, 15:42:08

#3

Prostavaryo

rob33n adlı üyeden alıntı: mesajı görüntüle

UNION, UNION ALL kullanarak yapabilirsin. Temp table kullanarakta yapabilirsin.

select country, GETDATE(), COUNT(host_name) as 'Downloads', '0' as 'Premium' from sites
union all
select country, GETDATE(), '0' as 'Downloads', COUNT(country) as 'Premium' from transactions

Temp table ile;

create table #tempTable
( 
Country nvarchar(250), DateOnly datetime, Downloads int, Premium int
)

insert into #tempTable (Country, DateOnly, Downloads, Premium)


select country, GETDATE(), COUNT(host_name) as 'Downloads', '0' as 'Premium' from sites
union all
select country, GETDATE(), '0' as 'Downloads', COUNT(country) as 'Premium' from transactions

select * from #tempTable
drop table #tempTable

union alt alta yapıyor. yan yana olmasını istiyorum.

14-04-2015, 12:31:55

#4

ulmotl

Merhaba. Sanırım join ile yapabilirsin. Şu şekilde olabilir:
Select sites.country, DATE(sites.created_at) as DateOnly, COUNT(sites.host_name) AS Downloads,transactions.country2, DATE(transactions.created_at) as DateOnly2, count(country) as Premium from sites join transactions on transactions.type='premium' AND transactions.payment > 0 AND transactions.country = 'Turkey' where sites.country = 'TR'

Buna benzer bir yapı olmalı ama group by ları nasıl yaparsın denemek lazım. Belki bi fikir verir diye yazdım. iyi çalışmalar.