CODEWITHSUNDEEP
sqloracle-databasegroup-by
sameer
sameer

Reputation: 220

SQL to get values

am stuck in a sql problem. I need something like this

M S C

1 sent 12
1 checked 15
1 rejected 9
2 sent 20
2 checked 18
3 rejected 10

Where M stands for Month, S for Status and C for Count respectively. What i want to achieve is to group the above example by Month

Wanted:

M S C

1 sent     12
  checked  15
  rejected  9
2 sent     20
  checked  18
  rejected 10

EDIT 1

Right now the sql statement looks like this:

select month, status, sum(hit) as count
from myTable
where dateletter between "date from" and "date to"
group by month,status order by month, status

Edit 2

How would i achieve this too

         1  2

Sent     12 20

Checked  15 18

Rejected 9  10

where 1,2 represent the month values from database.

Upvotes: 1

Views: 144

Answers (3)

Benoit
Benoit

Reputation: 79185

Using the BREAK ON syntax of SQL*Plus :

> BREAK ON M

>           SELECT 1 m , 'sent' s   , 12 c FROM dual
2 UNION ALL SELECT 1   , 'checked'  , 15   FROM dual
3 UNION ALL SELECT 1   , 'rejected' , 9    FROM dual
4 UNION ALL SELECT 2   , 'sent'     , 20   FROM dual
5 UNION ALL SELECT 2   , 'checked'  , 18   FROM dual
6 UNION ALL SELECT 3   , 'rejected' , 10   FROM dual

         M S                 C
---------- -------- ----------
         1 sent             12
           checked          15
           rejected          9
         2 sent             20
           checked          18
         3 rejected         10

So, with your example:

SET LINESIZE 2000 TRIMSPOOL ON TRIM ON
BREAK ON MONTH
SPOOL file.txt

SELECT month, status, SUM(hit) AS count
  FROM myTable
 WHERE dateletter BETWEEN "date from" AND "date to"
 GROUP BY month, status
 ORDER BY month, status;

SPOOL OFF

Upvotes: 1

tbone
tbone

Reputation: 15473

Not exactly clear what you want from your example, but it sounds like you want to show the details for each month and status, but also want a group total for each month.

You can do this using a rollup analytic function in Oracle:

create table tst2
(
m number,
s varchar2(10),
hit number
);


insert into tst2 values (1,'sent',3);
insert into tst2 values (1,'checked',2);
insert into tst2 values (1,'rejected',4);
insert into tst2 values (2,'sent',6);
insert into tst2 values (2,'checked',1);
insert into tst2 values (2,'rejected',3);
commit;

select m,s,sum(hit) as cnt,
case
  when (grouping(s) = 1 and grouping(m) = 1) then 'All total'
  when (grouping(s) = 1 and grouping(m) = 0) then 'Month total'
  else null
end as "Comment"
from tst2
group by rollup(m,s);

And you'll get

     1 sent                3           
     1 checked             2           
     1 rejected            4           
     1                     9 Month total
     2 sent                6            
     2 checked             1           
     2 rejected            3           
     2                    10 Month total
                          19 All total

See here for Oracle docs RE: analytic and reporting functions.

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112392

SELECT month AS M, status AS S, SUM(hit) AS C
FROM myTable
WHERE dateletter BETWEEN "date from" AND "date to"
GROUP BY month, status
ORDER BY month, status

In the query you cannot group further, you will have to do this in a report. The query is OK and can serve as record source for a report. In such a report, you would insert a group header for each month.

You could however make a pivot query

SELECT *
FROM
  ( SELECT month AS M, status AS S, SUM(hit) AS C
    FROM myTable
    WHERE dateletter BETWEEN "date from" AND "date to"
    GROUP BY month, status
  )
  PIVOT (
    SUM(C)
    FOR S IN ('sent', 'checked', 'rejected')
  );

This would return the status in columns

M sent checked rejected
1  12   15       9
2  20   18      10

Upvotes: 3

Related Questions