Help in Aggregating columns

  • 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

  • See the link in my signature for information about how to post a question in such a way that people can and will help you.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • So you want the days of the week with the count of information below it? (pivot)

  • thanks for your reply, yes i want the days of the week to be column headers, with the count information below.

    Thanks

    Timotech

  • 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

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • Hi Chris, you're the man, it worked perfectly, Thanks so much

    Timotech

  • You're welcome, thanks for the generous feedback.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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

  • 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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • Thanks so much, i've been able to discover my mistake, your codes formating were very helpful.

    Thanks

    Timotech

  • 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