#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    5
    Rep Power
    0

    Question Difficult SQL Query


    So I need to make a query that gets the total color prints and the total black and white prints for each job number.

    The table has three columns:
    - jobno
    - color
    - pages
    jobno is the job number, color is either 'Yes' or 'No' depending on whether or not it was printed on a color printer, and pages is the total pages for that print. So as you can tell there are multiple cases of each job number. What I've gotten so far is:

    SELECT X.jobno as JobNumber, X.Q as BlackandWhite, Y.C as Color

    FROM (

    SELECT jobno, SUM(pages) as Q
    FROM feb2010
    WHERE color='No'
    GROUP BY jobno) as X

    INNER JOIN (

    SELECT jobno, SUM(pages) as C
    FROM feb2010
    WHERE color='Yes'
    GROUP BY jobno) as Y

    ON X.jobno = Y.jobno

    ORDER BY X.jobno;

    The unfortunate part about this query is that since every job does not have a color print AND a black and white print, some of the jobs are eliminated from the table and only those with at least one of each is shown.

    Since the table is output by the print tracking software it's format cannot be altered.

    This query is supposed to output to a report (I'm writing this in Access) and I would like to be able to get both black and white in one report with one query. If not I can just separate them.

    Thanks for any help,

    Wryte
  2. #2
  3. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,952
    Rep Power
    4033
    Been a while since I worked with mysql, but off hand one of these queries may get you started on what you want.

    Code:
    SELECT
    	jobno,
    	color,
    	SUM(pages) as numPages
    FROM feb2010
    GROUP BY 
    	jobno,
    	color
    That would return one row for each color and black and white.

    Code:
    SELECT
    	jobno,
    	SUM(CASE WHEN color='Yes' THEN pages ELSE 0 END) as numColor,
    	SUM(CASE WHEN color='No' THEN pages ELSE 0 END) as numBlackWhite
    FROM feb2010
    GROUP BY
    	jobno
    That may return them as separate columns in the same row. Not sure.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    5
    Rep Power
    0
    Originally Posted by kicken
    Been a while since I worked with mysql, but off hand one of these queries may get you started on what you want.

    Code:
    SELECT
    	jobno,
    	color,
    	SUM(pages) as numPages
    FROM feb2010
    GROUP BY 
    	jobno,
    	color
    That would return one row for each color and black and white.

    Code:
    SELECT
    	jobno,
    	SUM(CASE WHEN color='Yes' THEN pages ELSE 0 END) as numColor,
    	SUM(CASE WHEN color='No' THEN pages ELSE 0 END) as numBlackWhite
    FROM feb2010
    GROUP BY
    	jobno
    That may return them as separate columns in the same row. Not sure.
    Your first query worked but it returned them in separate rows. The second one would not run, I was getting an error on the WHEN clause, I'm not sure if this is because the SQL in Access does not support it? I'm not really familiar with CASE, I've never used it.

    The given error:

    Syntax error (missing operator) in query expression 'SUM(CASE WHEN color='Yes THEN pages ELSE END)'.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by Wryte
    I'm not sure if this is because the SQL in Access does not support it? .
    dude, you posted in the MySQL forum

    MySQL is a different database than MS Access, and uses similar but different sql

    i'll move the thread to the appropriate forum for you

    meanwhile, change this --
    Code:
    SUM(CASE WHEN color='Yes' THEN pages ELSE 0 END)
    to this --
    Code:
    SUM(IIF(color='Yes',pages,0))

    Comments on this post

    • Wryte agrees : Thanks for the proper syntax, and sorry for the wrong post, didn't realize that access was that different from MySQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    5
    Rep Power
    0
    Alright so the plot thickens...

    I now have another table that gets output from a different print tracking system for the plotter. It tracks which type of paper is used (bond or vellum), how many sheets, and the job number. Technically the project numbers in the black and white/color table should be the same as what is in this xerox plotter table.

    What i want to do is add a column to this select that totals the vellum prints for the job and another column that totals the bond prints for the job. Is this possible?

    Here is the current query:

    SELECT [June-2011].jobno,

    Sum(IIf(color='Yes',pages,0)) AS Color,

    Sum(IIf(color='No',pages,0)) AS BW,

    FROM [June-2011]
    GROUP BY [June-2011].jobno;

    The table that has i will be adding is xeroxjune2011. With columns jobno, sheets, type (bond/vellum).
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Code:
    SELECT jobno
         , SUM(c) AS Color
         , SUM(w) AS BW
         , SUM(b) AS Bond
         , SUM(v) AS Vellum
      FROM ( SELECT jobno
                  , Sum(IIf(color='Yes',pages,0)) AS c
                  , Sum(IIf(color='No',pages,0))  AS w
                  , 0                             AS b
                  , 0                             AS v
               FROM [June-2011]
             GROUP 
                 BY jobno 
             UNION ALL
             SELECT jobno
                  , 0
                  , 0
                  , Sum(IIf(type='bond',sheets,0)) 
                  , Sum(IIf(type='vellum',sheets,0)) 
               FROM xeroxjune2011
             GROUP 
                 BY jobno ) AS x
    GROUP
        BY jobno

    Comments on this post

    • Wryte agrees : wizardry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    5
    Rep Power
    0
    I think that i understand for the most part, but can you refresh my memory as to how UNION ALL works?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by Wryte
    ... but can you refresh my memory as to how UNION ALL works?
    it returns a single result set which is the combined result sets of all the SELECTs (in this case, two) in the query

    UNION ALL means there is no automatic attempt to detect and remove duplicate rows (UNION or UNION DISTINCT would remove duplicate rows)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo