SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Report Fields

  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Report Fields

    I am having problem combining multiplie quries into one


    Code MySQL:
    SELECT username, count(*) as SuccessfulTotal, sum(point) as SuccessfulPoints FROM sales where submit_date >= 1301612400 AND submit_date <= 1304204400 AND status = 1 group by username

    Code MySQL:
    SELECT count(*) as Return FROM sales where submit_date >= 1301612400 AND submit_date <= 1304204400 AND status = 2  group by username

    So in the report table I will have 3 fields

    Username | SuccessfulTotal | SuccessfulPoints | Return
    [Home Sweet Home]

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Shouldnt it be....

    Code MySQL:
    SELECT COUNT(s1.point) AS return, COUNT(s2.point) AS SuccessfulTotal, SUM(s2.point) AS SuccessfulPoints 
    FROM sales AS s1 
    INNER JOIN sales AS s2 ON s1.username = s2.username 
    WHERE submit_date >= 1301612400 
    AND submit_date <= 1304204400 
    AND s1.status = 2 
    AND s2.status = 1  
    GROUP BY username <Probably should have an ORDER BY in here too somewhere...>
    ?

    (My MySQL brain isnt firing too well today apparently, so i'll expect r937 to come along in a second and correct me ;P)

  3. #3
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    Shouldnt it be....

    Code MySQL:
    SELECT COUNT(s1.point) AS return, COUNT(s2.point) AS SuccessfulTotal, SUM(s2.point) AS SuccessfulPoints 
    FROM sales AS s1 
    INNER JOIN sales AS s2 ON s1.username = s2.username 
    WHERE submit_date >= 1301612400 
    AND submit_date <= 1304204400 
    AND s1.status = 2 
    AND s2.status = 1  
    GROUP BY username <Probably should have an ORDER BY in here too somewhere...>
    ?

    (My MySQL brain isnt firing too well today apparently, so i'll expect r937 to come along in a second and correct me ;P)

    Thank you

    I think there is a mistake in your SQL?

    status = 2 show number of sales returns total. Not the point.

    status = 1 show numbers of sales and points.
    [Home Sweet Home]

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT username
         , SUM(CASE WHEN status = 1
                    THEN 1
                    ELSE NULL END) as SuccessfulTotal
         , SUM(CASE WHEN status = 1
                    THEN point
                    ELSE NULL END) as SuccessfulPoints 
         , SUM(CASE WHEN status = 2
                    THEN 1
                    ELSE NULL END) as Return
      FROM sales 
     WHERE submit_date BETWEEN 1301612400 AND 1304204400 
    GROUP 
        BY username
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT username
         , SUM(CASE WHEN status = 1
                    THEN 1
                    ELSE NULL END) as SuccessfulTotal
         , SUM(CASE WHEN status = 1
                    THEN point
                    ELSE NULL END) as SuccessfulPoints 
         , SUM(CASE WHEN status = 2
                    THEN 1
                    ELSE NULL END) as Return
      FROM sales 
     WHERE submit_date BETWEEN 1301612400 AND 1304204400 
    GROUP 
        BY username
    thanks Working great...

    Is there an alternative way too?

    [Home Sweet Home]

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by PowerStrike View Post
    Is there an alternative way too?
    yeah, but it's way more complex
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yeah, but it's way more complex
    How do you feel about this:

    Code MySQL:
    SELECT 
       distinct(outer_sales.username), 
       (SELECT count(*) as Points FROM sales where status = 1 AND username = outer_sales.username) as TotalSales,
       (SELECT sum(point) as Points FROM sales where status = 1 AND username = outer_sales.username) as Points,
       (SELECT count(*) FROM sales where status = 2 AND username = outer_sales.username) as Return,
       (SELECT count(*) FROM sales where (status = 3 OR status = 6) AND username = outer_sales.username) as UnsuccessfulSales
    FROM 
       sales outer_sales
    ORDER BY
       outer_sales.username;
    [Home Sweet Home]

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by PowerStrike View Post
    How do you feel about this:
    nauseous
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •