Je crée une fonctionnalité pour mon application dans laquelle je dois générer un rapport pour l'ensemble de 2018.
Je dois compter tous les billets pour 2018. Chaque billet a une catégorie. Par exemple: Changement de nom. Senior Citizen etc.
J'ai besoin de compter le nombre de tickets de changement de nom, de tickets seniors pour 2018 par mois
J'ai essayé mais je n'arrive pas à obtenir le résultat que je veux.
Je n'arrive pas à ventiler le décompte par mois.
Voici la requête que j'ai jusqu'à présent:
CATEGORY | JAN | FEB | MARCH | APRIL | MAY | JNE | JUL | AUG | SEPT | OCT| NOV| DEC SENIOR 2 5 20 50 1 11 23 4 1 2 4 6 COAN 23 55 22 55 6 2 12 23 12 12 5 89
Voici les colonnes que je veux voir:
SELECT SUBCATEGORY,COUNT(ticket_no) FROM CNR_TICKET WHERE date_created >= TO_DATE('1/01/2018','MM/DD/YYYY') AND date_created <= TO_DATE('12/31/2018','MM/DD/YYYY') GROUP BY SUBCATEGORY;
3 Réponses :
Quelque chose comme ceci:
EXTRACT(year FROM date_created ) = 2018
vous pouvez modifier votre clause WHERE
en utilisant:
SELECT SUBCATEGORY, count( distinct case when EXTRACT(month FROM date_created) = 1 then ticket_no else null end) as JAN, count( distinct case when EXTRACT(month FROM date_created) = 2 then ticket_no else null end) as FEB, count( distinct case when EXTRACT(month FROM date_created) = 3 then ticket_no else null end) as MARCH, count( distinct case when EXTRACT(month FROM date_created) = 4 then ticket_no else null end) as APRIL, count( distinct case when EXTRACT(month FROM date_created) = 5 then ticket_no else null end) as MAY, count( distinct case when EXTRACT(month FROM date_created) = 6 then ticket_no else null end) as JNE, count( distinct case when EXTRACT(month FROM date_created) = 7 then ticket_no else null end) as JUL, count( distinct case when EXTRACT(month FROM date_created) = 8 then ticket_no else null end) as AUG, count( distinct case when EXTRACT(month FROM date_created) = 9 then ticket_no else null end) as SEPT, count( distinct case when EXTRACT(month FROM date_created) = 10 then ticket_no else null end) as OCT, count( distinct case when EXTRACT(month FROM date_created) = 11 then ticket_no else null end) as NOV, count( distinct case when EXTRACT(month FROM date_created) = 12 then ticket_no else null end) as DEC FROM CNR_TICKET WHERE date_created >= to_date('1/01/2018','MM/DD/YYYY') and date_created <= to_date('12/31/2018','MM/DD/YYYY') GROUP BY SUBCATEGORY
Merci! Le décompte ajoute 1 à 4 mais il est déjà proche des résultats réels.
Vous pouvez essayer l'instruction PIVOT
select * from ( select SUBCATEGORY, month(date_created) mon from CNR_TICKET where date_created >= to_date('1/01/2018','MM/DD/YYYY') and date_created <= to_date('12/31/2018','MM/DD/YYYY') ) pivot ( count(*) for mon in ( 1 Jan, 2 Feb, 3 MARCH, 4 APRIL, 5 MAY, 6 JNE, 7 JUL, 8 AUG, 9 SEPT, 10 OCT, 11 NOV, 12 DEC ) )
Merci! mais cela ne fonctionne pas pour moi, je pense que la fonction mois ne peut pas être utilisée dans oracle
vous pouvez utiliser le mot clé Pivot
en utilisant for month
pour la requête pivotante comme
select subcategory, sum(case when to_char(date_created,'mm') = '01' then 1 else 0 end) as jan, sum(case when to_char(date_created,'mm') = '02' then 1 else 0 end) as feb, sum(case when to_char(date_created,'mm') = '03' then 1 else 0 end) as mar, sum(case when to_char(date_created,'mm') = '04' then 1 else 0 end) as apr, sum(case when to_char(date_created,'mm') = '05' then 1 else 0 end) as may, sum(case when to_char(date_created,'mm') = '06' then 1 else 0 end) as jun, sum(case when to_char(date_created,'mm') = '07' then 1 else 0 end) as jul, sum(case when to_char(date_created,'mm') = '08' then 1 else 0 end) as aug, sum(case when to_char(date_created,'mm') = '09' then 1 else 0 end) as sep, sum(case when to_char(date_created,'mm') = '10' then 1 else 0 end) as oct, sum(case when to_char(date_created,'mm') = '11' then 1 else 0 end) as nov, sum(case when to_char(date_created,'mm') = '12' then 1 else 0 end) as dec from cnr_ticket where to_char(date_created,'yyyy')='2018' group by subcategory
ou en utilisant agrégation conditionnelle
select * from ( select subcategory, to_char(date_created,'mm') as month from cnr_ticket where to_char(date_created,'yyyy')='2018' ) pivot( count(*) for (month) in ('01' as jan ,'02' as feb, '03' as mar, '04' as apr ,'05' as may, '06' as jun, '07' as jul ,'08' as aug, '09' as sep, '10' as oct ,'11' as nov, '12' as dec ) )
Merci! c'est proche des données réelles dont j'ai besoin, le décompte ajoute 1 à 4 lignes, c'est proche donc merci!
Veuillez publier la structure de votre table, quelques exemples de données et la sortie attendue pour ces données