Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: Alternating rows w/ SQL?

    I got a report from someone, and I am wondering if there is a way to replicate the display with SQL.

    It looks like this

    2004 1102 1320
    22% 21%
    2005 1000 1540
    44% 46%

    Basically the data is grouped by year, one row shows a total while the other row shows a percent.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you provided sample data, it would be easier (I guess) ... otherwise, this query might return the result you posted:
    Code:
    SQL> SELECT '2004 1102 1320' col FROM dual
      2  UNION ALL
      3  SELECT '22% 21%' FROM dual
      4  UNION ALL
      5  SELECT '2005 1000 1540' FROM dual
      6  UNION ALL
      7  SELECT '44% 46%' FROM dual;
    
    COL
    --------------
    2004 1102 1320
    22% 21%
    2005 1000 1540
    44% 46%
    
    SQL>
    I doubt that's what you want, but - what on Earth is "22%"? 22 percent of what? What makes 100% Where did 1102 come from?

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you'll post the test data INSERT statements & SQL that produces the desired results in 1 line, I'll post a solution that produces the desired 2 line output.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2004
    Posts
    83
    Here is a short example of how I am tallying totals and percentages. Thanks in advance for your help.

    SELECT fiscal_year, count(status) as total,
    count(case when status = 'M' then 1 else null end) as male,
    count(case when status = 'F' then 1 else null end) as female,
    (count(case when status = 'M' then status end) / count(status) *100) as male_percent,
    (count(case when status = 'F' then status end) / count(status) *100) as female_percent

    FROM hr_status
    WHERE fiscal_year IN ('2007','2006') and status IN ('M','F')
    GROUP BY fiscal_year

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Here is one way to do it :
    Code:
    SELECT fiscal_year_displayed AS year,
        male,
        female
    FROM
    (
        SELECT fiscal_year, 
            1 AS row_order,
            fiscal_year AS fiscal_year_displayed,
            COUNT(status) AS total,
            TO_CHAR(COUNT(CASE WHEN status = 'M' THEN 1 ELSE NULL END)) AS male,
            TO_CHAR(COUNT(CASE WHEN status = 'F' THEN 1 ELSE NULL END)) AS female
        FROM hr_status
        WHERE fiscal_year IN ('2007','2006') and status IN ('M','F')
        GROUP BY fiscal_year
        UNION ALL
        SELECT fiscal_year, 
            2 AS row_order,
            NULL AS fiscal_year_displayed,
            NULL AS total,
            TO_CHAR(ROUND(COUNT(CASE WHEN status = 'M' THEN status END) / COUNT(status) * 100))||'%' AS male,
            TO_CHAR(ROUND((COUNT(CASE WHEN status = 'F' THEN status END) / COUNT(status) * 100)))||'%' AS female
        FROM hr_status
        WHERE fiscal_year IN ('2007','2006') AND status IN ('M','F')
        GROUP BY fiscal_year
    )
    ORDER BY fiscal_year, row_order;
    Code:
    rbaraer@Ora10g> CREATE TABLE hr_status(
        fiscal_year VARCHAR2(4),
        status VARCHAR2(1));  2    3
    
    Table created.
    
    rbaraer@Ora10g> INSERT INTO hr_status(fiscal_year, status)
    SELECT '2006', 'M' FROM DUAL
      2    3  UNION ALL
      4  SELECT '2006', 'M' FROM DUAL
      5  UNION ALL
      6  SELECT '2006', 'F' FROM DUAL
      7  UNION ALL
      8  SELECT '2007', 'F' FROM DUAL
    UNION ALL
    SELECT '2007', 'M' FROM DUAL
      9   10   11  UNION ALL
     12  SELECT '2007', 'F' FROM DUAL
    UNION ALL
     13   14  SELECT '2007', 'F' FROM DUAL;
    
    7 rows created.
    
    rbaraer@Ora10g> SELECT fiscal_year_displayed AS year,
        male,
      2    3      female
      4  FROM
      5  (
      6      SELECT fiscal_year,
      7          1 AS row_order,
      8          fiscal_year AS fiscal_year_displayed,
      9          COUNT(status) AS total,
     10          TO_CHAR(COUNT(CASE WHEN status = 'M' THEN 1 ELSE NULL END)) AS male,
            TO_CHAR(COUNT(CASE WHEN status = 'F' THEN 1 ELSE NULL END)) AS female
     11   12      FROM hr_status
     13      WHERE fiscal_year IN ('2007','2006') and status IN ('M','F')
     14      GROUP BY fiscal_year
     15      UNION ALL
     16      SELECT fiscal_year,
     17          2 AS row_order,
     18          NULL AS fiscal_year_displayed,
     19          NULL AS total,
     20          TO_CHAR(ROUND(COUNT(CASE WHEN status = 'M' THEN status END) / COUNT(status) * 100))||'%' AS male,
            TO_CHAR(ROUND((COUNT(CASE WHEN status = 'F' THEN status END) / COUNT(status) * 100)))||'%' AS female
        FROM hr_status
        WHERE fiscal_year IN ('2007','2006') AND status IN ('M','F')
        GROUP BY fiscal_year
    )
    ORDER BY fiscal_year, row_order; 21   22   23   24   25   26
    
    YEAR MALE   FEMALE
    ---- ------ ------
    2006 2      1
         67%    33%
    2007 1      3
         25%    75%
    
    rbaraer@Ora10g>
    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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