Professional Web Applications Themes

Group by, count, type of query beyond my ability - Microsoft SQL / MS SQL Server

Hi group, I can't figure out this query that I want. My database is as such: Table: Articles ArticleID Description Table: BuiltArticles BuiltArticleID ArticleID PublicationID ''internet, intranet, etc. Status (tinyint) The basic premise is that an article in the articles table consists just of the content of the article. This content is then used to generate 1-50 .doc files that have some varying images or whatever based on what company they are for. Does that make sense? So there can be up to five files generated for each article that is entered into the database. The filenames are stored in ...

  1. #1

    Default Group by, count, type of query beyond my ability

    Hi group,

    I can't figure out this query that I want. My database is as such:

    Table: Articles
    ArticleID
    Description




    Table: BuiltArticles
    BuiltArticleID
    ArticleID
    PublicationID ''internet, intranet, etc.
    Status (tinyint)




    The basic premise is that an article in the articles table consists just of
    the content of the article. This content is then used to generate 1-50 .doc
    files that have some varying images or whatever based on what company they
    are for. Does that make sense? So there can be up to five files generated
    for each article that is entered into the database. The filenames are
    stored in the BuiltArticles table, and these articles are either published
    to our intranet, our Internet web site, or both. That is what the Status
    column is for. 0 = unpublished, 1 = published, 2 = deferred


    I'd like to return a recordset that would list the records like this:

    Description Number U/P/D (Unpublished/Published/Deferred)
    Article1 2/34/3
    Article2 0/39/2
    Article3 0/50/0
    Article4 50/0/0



    What that means is that, for Article1, there are 2+34+3=39 built .doc files
    logged in BuiltArticles table. 2 of them have a status of 0 (unpublished),
    34 have a status of 1 (published), and 3 have a status of 2 (deferred).
    Article2 has 0, 39, and 2. etc.

    I know how I can do this by looping through one recordset and querying some
    counts for each ArticleID returned from the Articles table, but I'm
    wondering if there is a way to do this all with one query.

    Thanks for all help,

    Ray at home




    Ray at Guest

  2. #2

    Default Re: Group by, count, type of query beyond my ability

    I think I got it. I will post it here in case there is something wrong.
    Otherwise, thank you again.

    SELECT Articles.Description,P1.*, (P1.u + P1.p + P1.d) AS Total
    FROM (SELECT
    SUM(CASE P.status WHEN 0 THEN 1 ELSE 0 END) AS u,
    SUM(CASE P.status WHEN 1 THEN 1 ELSE 0 END) AS p,
    SUM(CASE P.status WHEN 2 THEN 1 ELSE 0 END) AS d,
    P.ArtcieID
    FROM BuiltArticles AS P
    GROUP BY P.ArticleID)
    AS P1 INNER JOIN Articles ON Articles.ArticleID=P1.ArticleID

    Thank you,

    Ray at home


    "Ray at <%=sLocation%>" <rayajf8jalskdfna.sefrhja7yasdf.com> wrote in
    message news:e5rmwuPRDHA.1552TK2MSFTNGP10.phx.gbl...
    > That is outstanding Ray, and I even understand most of it. There is one
    > thing that I do not understand, and that is the "SELECT YEAR." I don't
    have
    > a column named year, but what I imagine that is supposed to be is my
    > Description from my Articles table. When I replace YEAR with
    p.Description,
    > this obviously does not work, as description is not in P (BuiltArticles).
    > And I cannot do Articles.Description.
    >
    > Would you happen to know how I can also pull in the description column
    from
    > the articles table?
    >
    > Thanks so much.
    >
    > Ray at home
    >
    >
    > "Ray Higdon" <rayhigdonhigdonconsulting.com> wrote in message
    > news:7fc601c344f9$e2c28450$a401280aphx.gbl...
    > > Try this (untested)
    > >
    > > SELECT P1.*, (P1.u + P1.p + P1.d) AS Total
    > > FROM (SELECT Year,
    > > SUM(CASE P.status WHEN 0 THEN 1 ELSE 0 END)
    > > AS u,
    > > SUM(CASE P.status WHEN 1 THEN 1 ELSE 0 END)
    > > AS p,
    > > SUM(CASE P.status WHEN 2 THEN 1 ELSE 0 END)
    > > AS d
    > > FROM builtarticles AS P
    > > GROUP BY P.articleid) AS P1
    > >
    > > HTH
    > >
    > > Ray Higdon MCSE, MCDBA, CCNA
    > >
    > > >-----Original Message-----
    > > >Hi group,
    > > >
    > > >I can't figure out this query that I want. My database
    > > is as such:
    > > >
    > > >Table: Articles
    > > >ArticleID
    > > >Description
    > > >
    > > >
    > > >
    > > >
    > > >Table: BuiltArticles
    > > >BuiltArticleID
    > > >ArticleID
    > > >PublicationID ''internet, intranet, etc.
    > > >Status (tinyint)
    > > >
    > > >
    > > >
    > > >
    > > >The basic premise is that an article in the articles
    > > table consists just of
    > > >the content of the article. This content is then used
    > > to generate 1-50 .doc
    > > >files that have some varying images or whatever based on
    > > what company they
    > > >are for. Does that make sense? So there can be up to
    > > five files generated
    > > >for each article that is entered into the database. The
    > > filenames are
    > > >stored in the BuiltArticles table, and these articles
    > > are either published
    > > >to our intranet, our Internet web site, or both. That
    > > is what the Status
    > > >column is for. 0 = unpublished, 1 = published, 2 =
    > > deferred
    > > >
    > > >
    > > >I'd like to return a recordset that would list the
    > > records like this:
    > > >
    > > >Description Number U/P/D
    > > (Unpublished/Published/Deferred)
    > > >Article1 2/34/3
    > > >Article2 0/39/2
    > > >Article3 0/50/0
    > > >Article4 50/0/0
    > > >
    > > >
    > > >
    > > >What that means is that, for Article1, there are
    > > 2+34+3=39 built .doc files
    > > >logged in BuiltArticles table. 2 of them have a status
    > > of 0 (unpublished),
    > > >34 have a status of 1 (published), and 3 have a status
    > > of 2 (deferred).
    > > >Article2 has 0, 39, and 2. etc.
    > > >
    > > >I know how I can do this by looping through one
    > > recordset and querying some
    > > >counts for each ArticleID returned from the Articles
    > > table, but I'm
    > > >wondering if there is a way to do this all with one
    > > query.
    > > >
    > > >Thanks for all help,
    > > >
    > > >Ray at home
    > > >
    > > >
    > > >
    > > >
    > > >.
    > > >
    >
    >

    Ray at Guest

  3. #3

    Default Re: Group by, count, type of query beyond my ability

    Didn't mean to leave that year in there, glad you caught
    it.

    Ray Higdon MCSE, MCDBA, CCNA
    >-----Original Message-----
    >I think I got it. I will post it here in case there is
    something wrong.
    >Otherwise, thank you again.
    >
    >SELECT Articles.Description,P1.*, (P1.u + P1.p + P1.d)
    AS Total
    >FROM (SELECT
    > SUM(CASE P.status WHEN 0 THEN 1 ELSE 0 END)
    AS u,
    > SUM(CASE P.status WHEN 1 THEN 1 ELSE 0 END)
    AS p,
    > SUM(CASE P.status WHEN 2 THEN 1 ELSE 0 END)
    AS d,
    > P.ArtcieID
    > FROM BuiltArticles AS P
    > GROUP BY P.ArticleID)
    > AS P1 INNER JOIN Articles ON
    Articles.ArticleID=P1.ArticleID
    >
    >Thank you,
    >
    >Ray at home
    >
    >
    >"Ray at <%=sLocation%>"
    <rayajf8jalskdfna.sefrhja7yasdf.com> wrote in
    >message news:e5rmwuPRDHA.1552TK2MSFTNGP10.phx.gbl...
    >> That is outstanding Ray, and I even understand most of
    it. There is one
    >> thing that I do not understand, and that is
    the "SELECT YEAR." I don't
    >have
    >> a column named year, but what I imagine that is
    supposed to be is my
    >> Description from my Articles table. When I replace
    YEAR with
    >p.Description,
    >> this obviously does not work, as description is not in
    P (BuiltArticles).
    >> And I cannot do Articles.Description.
    >>
    >> Would you happen to know how I can also pull in the
    description column
    >from
    >> the articles table?
    >>
    >> Thanks so much.
    >>
    >> Ray at home
    >>
    >>
    >> "Ray Higdon" <rayhigdonhigdonconsulting.com> wrote in
    message
    >> news:7fc601c344f9$e2c28450$a401280aphx.gbl...
    >> > Try this (untested)
    >> >
    >> > SELECT P1.*, (P1.u + P1.p + P1.d) AS Total
    >> > FROM (SELECT Year,
    >> > SUM(CASE P.status WHEN 0 THEN 1 ELSE 0
    END)
    >> > AS u,
    >> > SUM(CASE P.status WHEN 1 THEN 1 ELSE 0
    END)
    >> > AS p,
    >> > SUM(CASE P.status WHEN 2 THEN 1 ELSE 0
    END)
    >> > AS d
    >> > FROM builtarticles AS P
    >> > GROUP BY P.articleid) AS P1
    >> >
    >> > HTH
    >> >
    >> > Ray Higdon MCSE, MCDBA, CCNA
    >> >
    >> > >-----Original Message-----
    >> > >Hi group,
    >> > >
    >> > >I can't figure out this query that I want. My
    database
    >> > is as such:
    >> > >
    >> > >Table: Articles
    >> > >ArticleID
    >> > >Description
    >> > >
    >> > >
    >> > >
    >> > >
    >> > >Table: BuiltArticles
    >> > >BuiltArticleID
    >> > >ArticleID
    >> > >PublicationID ''internet, intranet, etc.
    >> > >Status (tinyint)
    >> > >
    >> > >
    >> > >
    >> > >
    >> > >The basic premise is that an article in the articles
    >> > table consists just of
    >> > >the content of the article. This content is then
    used
    >> > to generate 1-50 .doc
    >> > >files that have some varying images or whatever
    based on
    >> > what company they
    >> > >are for. Does that make sense? So there can be up
    to
    >> > five files generated
    >> > >for each article that is entered into the
    database. The
    >> > filenames are
    >> > >stored in the BuiltArticles table, and these
    articles
    >> > are either published
    >> > >to our intranet, our Internet web site, or both.
    That
    >> > is what the Status
    >> > >column is for. 0 = unpublished, 1 = published, 2 =
    >> > deferred
    >> > >
    >> > >
    >> > >I'd like to return a recordset that would list the
    >> > records like this:
    >> > >
    >> > >Description Number U/P/D
    >> > (Unpublished/Published/Deferred)
    >> > >Article1 2/34/3
    >> > >Article2 0/39/2
    >> > >Article3 0/50/0
    >> > >Article4 50/0/0
    >> > >
    >> > >
    >> > >
    >> > >What that means is that, for Article1, there are
    >> > 2+34+3=39 built .doc files
    >> > >logged in BuiltArticles table. 2 of them have a
    status
    >> > of 0 (unpublished),
    >> > >34 have a status of 1 (published), and 3 have a
    status
    >> > of 2 (deferred).
    >> > >Article2 has 0, 39, and 2. etc.
    >> > >
    >> > >I know how I can do this by looping through one
    >> > recordset and querying some
    >> > >counts for each ArticleID returned from the Articles
    >> > table, but I'm
    >> > >wondering if there is a way to do this all with one
    >> > query.
    >> > >
    >> > >Thanks for all help,
    >> > >
    >> > >Ray at home
    >> > >
    >> > >
    >> > >
    >> > >
    >> > >.
    >> > >
    >>
    >>
    >
    >
    >.
    >
    Ray Higdon Guest

Similar Threads

  1. Order by results of count using 'group by'
    By salvador in forum MySQL
    Replies: 3
    Last Post: August 2nd, 06:49 PM
  2. Count GROUP BY query dilemma
    By Bosconian in forum MySQL
    Replies: 5
    Last Post: December 23rd, 07:03 AM
  3. COUNT and GROUP
    By brianism in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 2nd, 09:09 PM
  4. ASP Group Same Records and Give Total Count
    By gotcha in forum ASP Database
    Replies: 1
    Last Post: August 6th, 08:50 PM
  5. Need Asp recordset Group / Count like values
    By gotcha in forum ASP Database
    Replies: 2
    Last Post: July 20th, 05:12 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139