Results 1 to 14 of 14

Thread: help on count rows.

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    help on count rows.

    Hi. SQL 2005.

    Code:
    SELECT TOP 5 Film_strTitleAlt
    FROM ZZ_vwpProgressBoard
    GROUP BY Film_strTitleAlt
    ORDER BY Sum(Admissions+CoolAdmissions)desc
    will give top 5 films. I want to get the total number or better yet i want to select all the other films except the top 5 (desc).
    Can't do:

    Code:
    SELECT  count(*)
    FROM ZZ_vwpProgressBoard
    GROUP BY Film_strTitleAlt
    as they are multiple films and i get 89 instead of 22 films.
    this:

    Code:
    SELECT  count(*)
    FROM ZZ_vwpProgressBoard
    GROUP BY Film_strTitleAlt
    will bring the 22 films but in 22 rows with different values.
    I just need one row with the total or better yet, as i've said all the other films except the top 5 (desc).
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: help on count rows.

    Hi,

    What you need to do is to create a Query which identifies the Top 5 Results and then Excludes these results from a Second Query. As an example have a play with this:-

    Code:
    SELECT TOP (100) PERCENT dbo.ZZ_vwpProgressBoard.Film_strTitleAlt
    FROM dbo.ZZ_vwpProgressBoard
    LEFT OUTER JOIN 
    (SELECT TOP (5) Film_strTitleAlt
     FROM            dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_1
     GROUP BY Film_strTitleAlt
     ORDER BY SUM(Admissions + CoolAdmissions) DESC) AS Top5Results
    ON dbo.ZZ_vwpProgressBoard.Film_strTitleAlt = Top5Results.Film_strTitleAlt
    WHERE (Top5Results.Film_strTitleAlt IS NULL)
    GROUP BY dbo.ZZ_vwpProgressBoard.Film_strTitleAlt
    Hope that helps.

    Cheers,

    Ian

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: help on count rows.

    Hi. Thanks, can i sum this up? something like:
    Code:
    SELECT sum(admissions) as admissions from(
    SELECT TOP (100) PERCENT --dbo.ZZ_vwpProgressBoard.Film_strTitleAlt,
      Sum(Admissions + CoolAdmissions) AS Admissions
        --   Sum(CoolAdmissions) AS CoolAdmissions
    FROM dbo.ZZ_vwpProgressBoard
    LEFT OUTER JOIN 
    (SELECT TOP (5) Film_strTitleAlt
     FROM            dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_1
     GROUP BY Film_strTitleAlt
     ORDER BY SUM(Admissions + CoolAdmissions) DESC) AS Top5Results
    ON dbo.ZZ_vwpProgressBoard.Film_strTitleAlt = Top5Results.Film_strTitleAlt
    WHERE (Top5Results.Film_strTitleAlt IS NULL)
    GROUP BY dbo.ZZ_vwpProgressBoard.Film_strTitleAlt)
    Ideally i would like the total in one row and it's percentage, so i can compare with other rows (well in this case the percentage would be 100% as is the only row)
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: help on count rows.

    Hi,

    Not sure which Percentage you are referring to but if you just want the Sum of Admissions then you can just say:-

    Code:
    SELECT        TOP (100) PERCENT SUM(dbo.ZZ_vwpProgressBoard.CoolAdmissions + dbo.ZZ_vwpProgressBoard.Admissions) AS Admissions
    FROM            dbo.ZZ_vwpProgressBoard LEFT OUTER JOIN
                                 (SELECT        TOP (5) Film_strTitleAlt
                                   FROM            dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_1
                                   GROUP BY Film_strTitleAlt
                                   ORDER BY SUM(Admissions + CoolAdmissions) DESC) AS Top5Results ON dbo.ZZ_vwpProgressBoard.Film_strTitleAlt = Top5Results.Film_strTitleAlt
    WHERE        (Top5Results.Film_strTitleAlt IS NULL)
    Cheers,

    Ian

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: help on count rows.

    Hey.
    Ideally i would like to see. sum total , sum admissions, sum cooladmissions , percentage.
    By percentage i mean the portion, percentage that all the above sum has in the the total or the WHOLE admissions, so also taken in consideration the TOP 5 that we missed here.
    I am not sure if we can do:
    1st top admission --- sum total , sum admissions, sum cooladmissions , percentage.
    2nd top admission --- sum total , sum admissions, sum cooladmissions , percentage.
    3rd top admission --- sum total , sum admissions, sum cooladmissions , percentage.
    4th top admission --- sum total , sum admissions, sum cooladmissions , percentage.
    5th top admission --- sum total , sum admissions, sum cooladmissions , percentage.
    All the other admissions --- sum total , sum admissions, sum cooladmissions , percentage. (this is the above query).

    I have made the all other admissions using the below but i am not sure how to incorporate the percentage and the other top 5 admissions. Or if is double with your sql above.
    Thanks
    Code:
    WITH Admissions
    AS
    (
    	SELECT Film_strTitleAlt
    		,SUM(Admissions + CoolAdmissions) TotalAdmissions
    		,sum(Admissions) as Admissions
    		,Sum(CoolAdmissions) AS CoolAdmissions
    	FROM ZZ_vwpProgressBoard
    	GROUP BY Film_strTitleAlt
    )
    ,AdmissionOrder
    AS
    (
    	SELECT Film_strTitleAlt, TotalAdmissions,Admissions,CoolAdmissions
    		,ROW_NUMBER() OVER (ORDER BY TotalAdmissions DESC) AS rn
    	FROM  Admissions
    )
    select sum(TotalAdmissions) TotalAdmissions ,sum(admissions) admissions,sum(CoolAdmissions) CoolAdmissions FROM AdmissionOrder
    WHERE rn > 5;
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: help on count rows.

    I think you almost have it... in order to calc the pct though, you need the totals...
    Code:
    WITH Totals
    AS
    (
    	SELECT SUM(Admissions + CoolAdmissions) TotalAdmissions
    		,sum(Admissions) as Admissions
    		,Sum(CoolAdmissions) AS CoolAdmissions
    	FROM ZZ_vwpProgressBoard
    )
    ,Admissions
    AS
    (
    	SELECT Film_strTitleAlt
    		,SUM(Admissions + CoolAdmissions) TotalAdmissions
    		,sum(Admissions) as Admissions
    		,Sum(CoolAdmissions) AS CoolAdmissions
    	FROM ZZ_vwpProgressBoard
    	GROUP BY Film_strTitleAlt
    )
    ,AdmissionOrder
    AS
    (
    	SELECT A.Film_strTitleAlt, A.TotalAdmissions,A.Admissions,A.CoolAdmissions, (A.TotalAdmissions / T.TotalAdmissions) as PctAdmissions
    		,ROW_NUMBER() OVER (ORDER BY A.TotalAdmissions DESC) AS rn
    	FROM  Admissions A
            CROSS APPLY Totals A
    )
    select TotalAdmissions, Admissions, CoolAdmissions, PctAdmissions
    FROM AdmissionOrder
    WHERE rn <= 5
    union 
    select sum(TotalAdmissions) TotalAdmissions, sum(Admissions) Admissions, sum(CoolAdmissions) CoolAdmissions, sum(PctAdmissions) PctAdmissions
    FROM AdmissionOrder
    WHERE rn > 5;
    See if that works... I'm not wholly convinced it is what you need... but it should be dang near close.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: help on count rows.

    Hi. I think you meant CROSS APPLY Totals T instead of CROSS APPLY Totals A ?
    Also this will put the not top 5 film result, randomly somewhere in the rows. How would i go and pull it to the last or first row?
    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  8. #8
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: help on count rows.

    Hi,

    Now I understand exactly what you are trying to achieve this is how I would go about it:-

    Create a query which Sums all the Admissions which can then be used to calculate the percentages.
    Create the Top 5 Film Query with the sums that you need and then Cross Join the Query in Point 1 to calculate the Percentages for each of the Top 5.
    Create the query which Sums all the other films Excluding the Top 5, Sum them to a single Record, and finally Cross Join the Query in Point 1 to once again calculate the Percentage of this group.
    Use UNION to join the two Querys together to produce the final result.

    This would then produce a final query something like:-

    sql Code:
    1. SELECT * FROM (SELECT        TOP (5) ZZ_vwpProgressBoard_1.Film_strTitleAlt, SUM(ZZ_vwpProgressBoard_1.Admissions) AS TotalAdmissions, SUM(ZZ_vwpProgressBoard_1.CoolAdmissions) AS TotalCoolAdmissions,
    2.                          SUM(ZZ_vwpProgressBoard_1.Admissions + ZZ_vwpProgressBoard_1.CoolAdmissions) AS TotalGroupValue, SUM(ZZ_vwpProgressBoard_1.Admissions + ZZ_vwpProgressBoard_1.CoolAdmissions)
    3.                          / MAX(OverallTotalTable.TotalAdmissionValue) * 100 AS PercentageOfTotalAdmissionValue
    4. FROM            dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_1 CROSS JOIN
    5.                              (SELECT        SUM(Admissions + CoolAdmissions) AS TotalAdmissionValue
    6.                                FROM            dbo.ZZ_vwpProgressBoard) AS OverallTotalTable
    7. GROUP BY ZZ_vwpProgressBoard_1.Film_strTitleAlt
    8. ORDER BY TotalGroupValue DESC) As Top5ResultsTable
    9.  
    10. UNION
    11.  
    12. SELECT        TOP (100) PERCENT MAX('All Other Films') AS OtherFilms, SUM(dbo.ZZ_vwpProgressBoard.Admissions) AS TotalAdmissions, SUM(dbo.ZZ_vwpProgressBoard.CoolAdmissions) AS TotalCoolAdmissions,
    13.                          SUM(dbo.ZZ_vwpProgressBoard.Admissions + dbo.ZZ_vwpProgressBoard.CoolAdmissions) AS TotalGroupValue, SUM(dbo.ZZ_vwpProgressBoard.Admissions + dbo.ZZ_vwpProgressBoard.CoolAdmissions)
    14.                          / MAX(OverallTotalTableAgain.TotalAdmissionValue) * 100 AS PercentageOfTotalAdmissionValue
    15. FROM            dbo.ZZ_vwpProgressBoard LEFT OUTER JOIN
    16.                              (SELECT        TOP (5) ZZ_vwpProgressBoard_1.Film_strTitleAlt, SUM(ZZ_vwpProgressBoard_1.Admissions) AS TotalAdmissions, SUM(ZZ_vwpProgressBoard_1.CoolAdmissions) AS TotalCoolAdmissions,
    17.                                                          SUM(ZZ_vwpProgressBoard_1.Admissions + ZZ_vwpProgressBoard_1.CoolAdmissions) AS TotalGroupValue,
    18.                                                          SUM(ZZ_vwpProgressBoard_1.Admissions + ZZ_vwpProgressBoard_1.CoolAdmissions) / MAX(OverallTotalTable.TotalAdmissionValue) * 100 AS PercentageOfTotalAdmissionValue
    19.                                FROM            dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_1 CROSS JOIN
    20.                                                              (SELECT        SUM(Admissions + CoolAdmissions) AS TotalAdmissionValue
    21.                                                                FROM            dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_2) AS OverallTotalTable
    22.                                GROUP BY ZZ_vwpProgressBoard_1.Film_strTitleAlt
    23.                                ORDER BY TotalGroupValue DESC) AS Top5Results ON dbo.ZZ_vwpProgressBoard.Film_strTitleAlt = Top5Results.Film_strTitleAlt CROSS JOIN
    24.                              (SELECT        SUM(Admissions + CoolAdmissions) AS TotalAdmissionValue
    25.                                FROM            dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_3) AS OverallTotalTableAgain
    26. WHERE        (Top5Results.Film_strTitleAlt IS NULL)
    27. ORDER BY PercentageOfTotalAdmissionValue DESC

    Hope that helps.

    Cheers,

    Ian

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: help on count rows.

    Quote Originally Posted by sapator View Post
    Hi. I think you meant CROSS APPLY Totals T instead of CROSS APPLY Totals A ?
    Correct, sorry about that.

    Quote Originally Posted by sapator View Post
    Also this will put the not top 5 film result, randomly somewhere in the rows. How would i go and pull it to the last or first row?
    Thanks.
    They shouldn't be random... they should be the first 5 rows, followed by a sixth row which has all of the others... if you're not getting that, then something is interfering.

    But...
    Code:
    select rn as SRC, TotalAdmissions, Admissions, CoolAdmissions, PctAdmissions
    FROM AdmissionOrder
    WHERE rn <= 5
    union 
    select 6 as SRC, sum(TotalAdmissions) TotalAdmissions, sum(Admissions) Admissions, sum(CoolAdmissions) CoolAdmissions, sum(PctAdmissions) PctAdmissions
    FROM AdmissionOrder
    WHERE rn > 5
    ORDER BY SRC
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: help on count rows.

    IanRyder that is great except i get zeros on percentages.
    Tech looking at this now will post back...
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  11. #11

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: help on count rows.

    Tech this will bring zero percentages and also i am trying to have the >5 value on top but anyhow if this too much to ask i would probably cut this down inside VB.NET.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  12. #12
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: help on count rows.

    maybe it would help if I could SEE what you're getting... all I have to go by is what you're telling us. Unfortunately I'm more of a visual type where a picture is worth a thousand words. I'm not sure what you mean by zero percents, since they would be rolled (or should be) into line 6... if you want to filter out 0 percents, then you can do that anywhere along the line after the pct has been calc... or add it anywhere since the only reason the pct wqould be zero, is if TotalAdmissions is zero.... so you can add TotalAdmissions<>0 to any of the where clauses along the way - I would do it as early as possible so that your data sets are limited as quickly as possible in the chain.

    A shot of what you're getting, what you expect, and maybe some sample data would be helpful

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: help on count rows.

    I get 0 on every percentage line.Tech, ok forget the percentages for now and everything else, as this is getting very complex for me.
    I have shorted everything i guess and the only problem i have is the below and i would appreciate a simple solution for the union and i am through with this.
    So i would like to join the 2 selects but when i use the ORDER BY, I get "ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator."
    So i just need to know how will i bind that.No WITH no CROSS APPLY'S NO NOTHING. Just how would i get the union to work in the simplest way possible.
    Thanks:
    Code:
    select  CONVERT(VARCHAR,SUM(Admissions+CoolAdmissions)) as Cinema_DisplayName 
    ,null AS Admissions,sum(CoolAdmissions) AS CoolAdmissions,null as Percentage from ZZ_vwpProgressBoard
    Union
    SELECT Cinema_DisplayName, 
           Sum(Admissions) AS Admissions,
           Sum(CoolAdmissions) AS CoolAdmissions,	
    CASE
    	when 
        CHARINDEX('.',(LEFT(count(*) * 100.0 / (select count(*) from ZZ_vwpProgressBoard),5)),3) > 0 THEN LEFT(count(*) * 100.0 / (select count(*) from ZZ_vwpProgressBoard),5)
    	ELSE LEFT(count(*) * 100.0 / (select count(*) from ZZ_vwpProgressBoard),4) 	
    END as Percentage	
    FROM ZZ_vwpProgressBoard
    GROUP BY Cinema_DisplayName
    -- if used will hit error
    ORDER BY MAX(Cinema_Order)
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  14. #14
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: help on count rows.

    MAX(Cinema_Order) needs to appear in both select statements of the union. The ORDER BY applies to the WHOLE data set... not just the last one. The WHOLE set. So if you order by something, it has to EXIST in the first select and any other selects that you union. It will execute the first select, followed by the second, put the results together, then attempt to sort. That's why I had the "rn as SRC" and the "6 as SRC" and then the Order by SRC in the example I was giving.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width