July 19, 2010 at 10:29 am
Hi All, I think i am stuck and i need help. What i want to achieve is this:
Category Station Brand Sun Mon Tue
TELECOMMUNICATIONSA.I.T CHANNEL 49ETISALAT(EMTS)11 -
TELECOMMUNICATIONSA.I.T CHANNEL 49MTN 1 2 5
TELECOMMUNICATIONSA.I.T CHANNEL 49ZAIN 3 4 6
TELECOMMUNICATIONS A.I.T CHANNEL 49 GLOBACOM 2 3 4
TELECOMMUNICATIONSABS TV AWKAETISALAT(EMTS)15 2
TELECOMMUNICATIONSABS TV AWKAGLOBACOM16 3
TELECOMMUNICATIONSABS TV AWKAMTN 12 1
TELECOMMUNICATIONSABS TV AWKAZAIN 16 1
I.e I want a situation whereby The Brands are summed based on the particular days it occurs in that format.
I have this sql that does similar, but i can't seem to get that Sun, Mon, Tue, ... to work.
SELECT TOP (100) PERCENT dbo.tbl_ProductCategories.Description AS Category, dbo.tbl_Stations.Description AS Station, dbo.tbl_Brand.Description AS Brand,
COUNT(dbo.tbl_Brand.Description) AS [Number Of Occurence], LEFT(DATENAME(dw, dbo.tbl_Television.AdDate), 3) AS Day
FROM dbo.tbl_ProductCategories INNER JOIN
dbo.tbl_Television ON dbo.tbl_ProductCategories.CategoryId = dbo.tbl_Television.FK_CategoryId INNER JOIN
dbo.tbl_Stations ON dbo.tbl_Television.FK_StationId = dbo.tbl_Stations.StationId LEFT OUTER JOIN
dbo.tbl_Brand ON dbo.tbl_Television.FK_BrandId = dbo.tbl_Brand.BrandId AND dbo.tbl_Television.FK_ProductId = dbo.tbl_Brand.FK_ProductId
WHERE (dbo.tbl_Television.FK_CategoryId = @CategoryId) AND (dbo.tbl_Television.AdDate BETWEEN @BeginDate AND @EndDate)
GROUP BY dbo.tbl_ProductCategories.Description, dbo.tbl_Stations.Description, dbo.tbl_Brand.Description, dbo.tbl_Television.AdDate
ORDER BY Station, Brand
Please any help is appreciated.
Thanks
Timotech
July 19, 2010 at 12:42 pm
July 19, 2010 at 12:52 pm
So you want the days of the week with the count of information below it? (pivot)
July 20, 2010 at 1:09 am
thanks for your reply, yes i want the days of the week to be column headers, with the count information below.
Thanks
Timotech
July 20, 2010 at 4:20 am
Hi thanks for your hints, let me repost the data like this, may be u'll understand, because there's no existing table for that query:
CREATE TABLE #mytable(
Category nvarchar(35),
Station nvarchar(20),
Brand nvarchar(20),
Sun INT,
Mon INT,
Tue INT,
Wed INT,
Thu Int,
Fri Int,
Sat Int
)
INSERT INTO #mytable (Category,Station,Brand,Sun,Mon,Tue,...Sat)
SELECT 'TELECOMMUNICATIONS','A.I.T CHANNEL 49','ETISALAT(EMTS)', '1', '1', '-',UNION ALL
SELECT 'TELECOMMUNICATIONS','A.I.T CHANNEL 49','MTN', '1', '2', '5',UNION ALL
SELECT 'TELECOMMUNICATIONS','A.I.T CHANNEL 49','ZAIN', '3', '4', '6',UNION ALL
SELECT 'TELECOMMUNICATIONS','A.I.T CHANNEL 49','GLOBACOM', '2', '3', '4',UNION ALL
SELECT 'TELECOMMUNICATIONS','ABS TV AWKA', 'ETISALAT(EMTS)', '1', '5', '2',UNION ALL
SELECT 'TELECOMMUNICATIONS','ABS TV AWKA', 'GLOBACOM', '1', '6', '3',UNION ALL
SELECT 'TELECOMMUNICATIONS','ABS TV AWKA', 'MTN', '1', '2', '1',UNION ALL
SELECT 'TELECOMMUNICATIONS','ABS TV AWKA', 'ZAIN', '1', '6', '1',UNION ALL
But most important what i want to achieve is to be able to count the Brands that exist at particular days based on the tbl_Television.AdDate field.
Thanks very much for your help.
Timotech
July 20, 2010 at 4:57 am
Untested:
SELECT
d.Category,
d.Station,
d.Brand,
'Sun' = SUM(CASE d.[Day] WHEN 'Sun' THEN d.[Number Of Occurence] ELSE 0 END),
'Mon' = SUM(CASE d.[Day] WHEN 'Mon' THEN d.[Number Of Occurence] ELSE 0 END),
'Tue' = SUM(CASE d.[Day] WHEN 'Tue' THEN d.[Number Of Occurence] ELSE 0 END),
'Wed' = SUM(CASE d.[Day] WHEN 'Wed' THEN d.[Number Of Occurence] ELSE 0 END),
'Thu' = SUM(CASE d.[Day] WHEN 'Thu' THEN d.[Number Of Occurence] ELSE 0 END),
'Fri' = SUM(CASE d.[Day] WHEN 'Fri' THEN d.[Number Of Occurence] ELSE 0 END),
'Sat' = SUM(CASE d.[Day] WHEN 'Sat' THEN d.[Number Of Occurence] ELSE 0 END)
FROM (
SELECT
p.[Description] AS Category,
s.[Description] AS Station,
b.[Description] AS Brand,
COUNT(b.[Description]) AS [Number Of Occurence],
LEFT(DATENAME(dw, t.AdDate), 3) AS [Day]
FROM dbo.tbl_ProductCategories p
INNER JOIN dbo.tbl_Television t ON p.CategoryId = t.FK_CategoryId
INNER JOIN dbo.tbl_Stations s ON t.FK_StationId = s.StationId
LEFT OUTER JOIN dbo.tbl_Brand b ON t.FK_BrandId = b.BrandId
AND t.FK_ProductId = b.FK_ProductId
WHERE (t.FK_CategoryId = @CategoryId)
AND (t.AdDate BETWEEN @BeginDate AND @EndDate)
GROUP BY p.[Description], s.[Description], b.[Description], t.AdDate
) d
GROUP BY d.Category, d.Station, d.Brand
ORDER BY d.Station, d.Brand
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 20, 2010 at 5:52 am
Hi Chris, you're the man, it worked perfectly, Thanks so much
Timotech
July 20, 2010 at 6:28 am
You're welcome, thanks for the generous feedback.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2010 at 6:58 am
Hi Chris thanks for your previous help, please i tried this:
SELECT TOP (100) PERCENT
(SELECT DISTINCT dbo.tbl_ProductCategories.Description
FROM dbo.tbl_Product INNER JOIN
dbo.tbl_ProductCategories ON dbo.tbl_Product.FK_CategoryId = dbo.tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Product.ProductId = @ProductId)) AS Category, Medium, Brand,
SUM(CASE d .[Month] WHEN 'Jan' THEN d .[TotalAmount] ELSE 0 END) AS 'Jan',
SUM(CASE d .[Month] WHEN 'Feb' THEN d .[TotalAmount] ELSE 0 END)
AS 'Feb',
SUM(CASE d .[Month] WHEN 'Mar' THEN d .[TotalAmount] ELSE 0 END) AS 'Mar',
SUM(CASE d .[Month] WHEN 'Apr' THEN d .[TotalAmount] ELSE 0 END) AS 'Apr',
SUM(CASE d .[Month] WHEN 'May' THEN d .[TotalAmount] ELSE 0 END)
AS 'May',
SUM(CASE d .[Month] WHEN 'Jun' THEN d .[TotalAmount] ELSE 0 END) AS 'Jun',
SUM(CASE d .[Month] WHEN 'Jul' THEN d .[TotalAmount] ELSE 0 END) AS 'Jul',
SUM(CASE d .[Month] WHEN 'Aug' THEN d .[TotalAmount] ELSE 0 END)
AS 'Aug',
SUM(CASE d .[Month] WHEN 'Sep' THEN d .[TotalAmount] ELSE 0 END) AS 'Sep',
SUM(CASE d .[Month] WHEN 'Oct' THEN d .[TotalAmount] ELSE 0 END) AS 'Oct',
SUM(CASE d .[Month] WHEN 'Nov' THEN d .[TotalAmount] ELSE 0 END)
AS 'Nov',
SUM(CASE d .[Month] WHEN 'Dec' THEN d .[TotalAmount] ELSE 0 END) AS 'Dec'
FROM ((SELECT TOP (100) PERCENT SUM(dbo.tbl_Television.Amount) AS TotalAmount, LEFT(DATENAME(mm, dbo.tbl_Television.AdDate), 3) AS Month,
dbo.tbl_Brand.Description AS Brand, 'TV Total' AS Medium
FROM dbo.tbl_Television INNER JOIN
dbo.tbl_Brand ON dbo.tbl_Television.FK_ProductId = dbo.tbl_Brand.FK_ProductId AND
dbo.tbl_Television.FK_BrandId = dbo.tbl_Brand.BrandId
WHERE (dbo.tbl_Television.FK_ProductId = @ProductId) AND (dbo.tbl_Television.AdDate BETWEEN @BeginDate AND @EndDate)
GROUP BY dbo.tbl_Brand.Description, dbo.tbl_Television.AdDate
ORDER BY Brand) AS TeleAmounts
UNION ALL
(SELECT TOP (100) PERCENT SUM(dbo.tbl_Radio.Amount) AS TotalAmount, LEFT(DATENAME(mm, dbo.tbl_Radio.AdDate), 3) AS Month,
dbo.tbl_Brand.Description AS Brand, 'Radio Total' AS Medium
FROM dbo.tbl_Radio INNER JOIN
dbo.tbl_Brand ON dbo.tbl_Radio.FK_ProductId = dbo.tbl_Brand.FK_ProductId AND
dbo.tbl_Radio.FK_BrandId = dbo.tbl_Brand.BrandId
WHERE (dbo.tbl_Radio.FK_ProductId = @ProductId) AND (dbo.tbl_Radio.AdDate BETWEEN @BeginDate AND @EndDate)
GROUP BY dbo.tbl_Brand.Description, dbo.tbl_Radio.AdDate
ORDER BY Brand) AS RadioAmounts) AS d
GROUP BY Brand, Medium
ORDER BY Brand, Medium
and its supposed to give me this:
Insert into #mytable(Category, Medium, Brand, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Nov, Dec)
SELECT 'TELECOMMUNICATIONS','TV Total', 'ETISALAT(EMTS)', '0', '0','0', '0', '62266962', ... UNION ALL
SELECT 'TELECOMMUNICATIONS','TV Total', 'GLOBACOM', '0', '0', '0', '0', '33127444', ... UNION ALL
SELECT 'TELECOMMUNICATIONS','TV Total', 'MTN', '0', '0', '0', '0', '149755493', ... UNION ALL
SELECT 'TELECOMMUNICATIONS','TV Total', 'VISAFONE', '0', '0', '0', '0', '78444', ... UNION ALL
SELECT 'TELECOMMUNICATIONS','Radio Total', 'ETISALAT(EMTS)', '0', '0', '0', '0', '62266962', ... UNION ALL
SELECT 'TELECOMMUNICATIONS','Radio Total', 'GLOBACOM', '0', '0', '0', '0', '33127444', ... UNION ALL
SELECT 'TELECOMMUNICATIONS','Radio Total', 'MTN', '0', '0', '0', '0', '149755493', ... UNION ALL
SELECT 'TELECOMMUNICATIONS','Radio Total', 'VISAFONE', '0', '0', '0', '0', '78444', ... UNION ALL
Please help thanks
Timotech
July 30, 2010 at 7:58 am
A little formatting goes a long way:
SELECT TOP (100) PERCENT
(SELECT DISTINCT dbo.tbl_ProductCategories.Description
FROM dbo.tbl_Product
INNER JOIN dbo.tbl_ProductCategories ON dbo.tbl_Product.FK_CategoryId = dbo.tbl_ProductCategories.CategoryId
WHERE (dbo.tbl_Product.ProductId = @ProductId)) AS Category,
Medium,
Brand,
SUM(CASE d .[Month] WHEN 'Jan' THEN d .[TotalAmount] ELSE 0 END) AS 'Jan',
SUM(CASE d .[Month] WHEN 'Feb' THEN d .[TotalAmount] ELSE 0 END) AS 'Feb',
SUM(CASE d .[Month] WHEN 'Mar' THEN d .[TotalAmount] ELSE 0 END) AS 'Mar',
SUM(CASE d .[Month] WHEN 'Apr' THEN d .[TotalAmount] ELSE 0 END) AS 'Apr',
SUM(CASE d .[Month] WHEN 'May' THEN d .[TotalAmount] ELSE 0 END) AS 'May',
SUM(CASE d .[Month] WHEN 'Jun' THEN d .[TotalAmount] ELSE 0 END) AS 'Jun',
SUM(CASE d .[Month] WHEN 'Jul' THEN d .[TotalAmount] ELSE 0 END) AS 'Jul',
SUM(CASE d .[Month] WHEN 'Aug' THEN d .[TotalAmount] ELSE 0 END) AS 'Aug',
SUM(CASE d .[Month] WHEN 'Sep' THEN d .[TotalAmount] ELSE 0 END) AS 'Sep',
SUM(CASE d .[Month] WHEN 'Oct' THEN d .[TotalAmount] ELSE 0 END) AS 'Oct',
SUM(CASE d .[Month] WHEN 'Nov' THEN d .[TotalAmount] ELSE 0 END) AS 'Nov',
SUM(CASE d .[Month] WHEN 'Dec' THEN d .[TotalAmount] ELSE 0 END) AS 'Dec'
FROM (
(SELECT TOP (100) PERCENT
SUM(dbo.tbl_Television.Amount) AS TotalAmount,
LEFT(DATENAME(mm, dbo.tbl_Television.AdDate), 3) AS Month,
dbo.tbl_Brand.Description AS Brand,
'TV Total' AS Medium
FROM dbo.tbl_Television
INNER JOIN dbo.tbl_Brand ON dbo.tbl_Television.FK_ProductId = dbo.tbl_Brand.FK_ProductId
AND dbo.tbl_Television.FK_BrandId = dbo.tbl_Brand.BrandId
WHERE (dbo.tbl_Television.FK_ProductId = @ProductId)
AND (dbo.tbl_Television.AdDate BETWEEN @BeginDate AND @EndDate)
GROUP BY dbo.tbl_Brand.Description, dbo.tbl_Television.AdDate
ORDER BY Brand) AS TeleAmounts
UNION ALL
(SELECT TOP (100) PERCENT
SUM(dbo.tbl_Radio.Amount) AS TotalAmount,
LEFT(DATENAME(mm, dbo.tbl_Radio.AdDate), 3) AS Month,
dbo.tbl_Brand.Description AS Brand,
'Radio Total' AS Medium
FROM dbo.tbl_Radio
INNER JOIN dbo.tbl_Brand ON dbo.tbl_Radio.FK_ProductId = dbo.tbl_Brand.FK_ProductId
AND dbo.tbl_Radio.FK_BrandId = dbo.tbl_Brand.BrandId
WHERE (dbo.tbl_Radio.FK_ProductId = @ProductId)
AND (dbo.tbl_Radio.AdDate BETWEEN @BeginDate AND @EndDate)
GROUP BY dbo.tbl_Brand.Description, dbo.tbl_Radio.AdDate
ORDER BY Brand) AS RadioAmounts
) AS d
GROUP BY Brand, Medium
ORDER BY Brand, Medium
Are you using a view designer or some other tool to generate this code, by any chance? It doesn't look hand written.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2010 at 8:08 am
Thanks so much, i've been able to discover my mistake, your codes formating were very helpful.
Thanks
Timotech
July 30, 2010 at 10:27 am
Hi Chris thanks for all your help. please if i want to add a percentage column to the whole show, how do i do it, for example, for this inner section:
SELECT SUM(t .Amount) AS TotalAmount, b.Description AS Brand, 'TV Total' AS Medium
FROM dbo.tbl_Television AS t INNER JOIN
dbo.tbl_Brand AS b ON t .FK_ProductId = b.FK_ProductId AND t .FK_BrandId = b.BrandId
WHERE (t .FK_ProductId = @ProductId) AND (t .AdDate BETWEEN @BeginDate AND @EndDate)
GROUP BY b.Description
ORDER BY Brand
I have this result:
Insert into #mytable(TotalAmount, Brand, Medium)
SELECT '62266962', 'ETISALAT(EMTS)', 'TV Total' UNION ALL
SELECT '33127444', 'GLOBACOM', 'TV Total' UNION ALL
SELECT '149755493', 'MTN', 'TV Total' UNION ALL
SELECT '78444', 'VISAFONE','TV Total' UNION ALL
SELECT '18456497', 'ZAIN', 'TV Total' UNION ALL
SELECT '194632', 'ZOOM MOBILE', 'TV Total' UNION ALL
what if i want a result that will display the percentages, such the percentage of each individual TotalAmount from the Sum(TotalAmount) e.g like this:
Insert into #mytable(TotalAmount, Brand, Medium, Percentages)
SELECT '62266962', 'ETISALAT(EMTS)', 'TV Total', 7% UNION ALL
SELECT '33127444', 'GLOBACOM', 'TV Total', 18% UNION ALL
SELECT '149755493', 'MTN', 'TV Total', '21%' UNION ALL
SELECT '78444', 'VISAFONE','TV Total', '12%' UNION ALL
SELECT '18456497', 'ZAIN', 'TV Total', '8%' UNION ALL
SELECT '194632', 'ZOOM MOBILE', 'TV Total', '13%' UNION ALL
something of this nature may be from a formula (TotalAmount/Sum(TotalAmount)) * 100
Thanks for your help, its a bit difficult for me to write.
Timotech
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply