Solved

SQL REQUEST PROBLEM

Posted on 2012-04-13
13
263 Views
Last Modified: 2012-04-18
hi,

I've this sql request :

SELECT CODE_FAMILLE,SUM(MONTANT) AS TOTAL,ANNEE
FROM import_stat_client_produit
WHERE CODE_CLIENT = 235
GROUP BY ANNEE,CODE_FAMILLE
ORDER BY CODE_FAMILLE

And the result is

BAND      6.75      N-1
CARR      74699.51      N
CARR      49860.48      N-1
COLL      78562.34      N
COLL      59139.02      N-1
DIV      233.04      N
DIV      -666.03      N-1
ENTR      72.66      N-1
EVAC      240.30      N
NATT      220.00      N
NATT      5550.00      N-1
OUT      1302.95      N
OUT      20.64      N-1
PRO      776.37      N
PRO      394.86      N-1
TRA      228.69      N
TRA      11.02      N-1

I would like to have this result

CODE_FAMILLE     N               N-1
BAND                      6.75          NULL
CARR                       74699.51   49860.48
COLL                        78562.34   59139.02

etc

How to do that
I've a SQL SERVER 2005 Express

Thanks for your help
0
Comment
  • 6
  • 3
  • 2
  • +2
13 Comments
 

Expert Comment

by:MHITSupport
ID: 37841728
Try

SELECT CODE_FAMILLE,SUM(MONTANT) AS TOTAL,ANNEE
FROM import_stat_client_produit
WHERE CODE_CLIENT = 235
GROUP BY CODE_FAMILLE
ORDER BY ANNEE

or

SELECT CODE_FAMILLE,SUM(MONTANT) AS TOTAL,ANNEE
FROM import_stat_client_produit
WHERE CODE_CLIENT = 235
GROUP BY CODE_FAMILLE,ANNEE
ORDER BY CODE_FAMILLE
0
 
LVL 6

Author Comment

by:Manuel Marienne-Duchêne
ID: 37841751
Not functionnaly
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37842038
SELECT t1.CODE_FAMILLE, SUM(t1.MONTANT) AS N, SUM(t2.MONTANT) AS [N-1]
FROM import_stat_client_produit t1 LEFT JOIN
    import_stat_client_produit t2 ON t1.CODE_FAMILLE = t2.CODE_FAMILLE
WHERE t1.ANNEE = 'N' AND t2.ANNEE = 'N-1'
GROUP BY t1.CODE_FAMILLE
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:Manuel Marienne-Duchêne
ID: 37842143
Almost

CODE_FAMILLE      N                    N-1
CARR      702175394.00      336451921.38
COLL      440840789.52      60277033.44
DIV      53133.12                      90549.44
EVAC      79539.30                  114376.65
NATT      59400.00                      56288.79
OUT      1986998.75      350592.21
PRO      1473550.26      452513.90
TRA      15550.92                      15316.40

But the sum is all the CODE_CLIENT NOT CODE_CLIENT = 235
0
 
LVL 5

Expert Comment

by:gman84
ID: 37842202
TRY:

SELECT CODE_FAMILLE,
      CASE ANNEE
            WHEN 'N' THEN SUM(MONTANT)
            ELSE 0
      END,
      CASE ANNEE
            WHEN 'N-1' THEN SUM(MONTANT)
      END
FROM import_stat_client_produit
WHERE CODE_CLIENT = 235
GROUP BY CODE_FAMILLE
ORDER BY CODE_FAMILLE
0
 
LVL 6

Author Comment

by:Manuel Marienne-Duchêne
ID: 37842218
Not good

the result is
BAND      0.00      6.75
CARR      74699.51      NULL
CARR      0.00      49860.48
COLL      78665.38      NULL
COLL      0.00      59139.02
DIV      233.04      NULL
DIV      0.00      -666.03
ENTR      0.00      72.66
EVAC      240.30      NULL
NATT      220.00      NULL
NATT      0.00      5550.00
OUT      1302.95      NULL
OUT      0.00      20.64
PRO      776.37      NULL
PRO      0.00      394.86
TRA      228.69      NULL
TRA      0.00      11.02
0
 
LVL 5

Expert Comment

by:gman84
ID: 37842226
Then try "matthewspatrick" query with a slight amendment:

SELECT t1.CODE_FAMILLE, SUM(t1.MONTANT) AS N, SUM(t2.MONTANT) AS [N-1]
FROM import_stat_client_produit t1 LEFT JOIN
    import_stat_client_produit t2 ON t1.CODE_FAMILLE = t2.CODE_FAMILLE
WHERE t1.ANNEE = 'N' AND t2.ANNEE = 'N-1'
    AND t1.CODE_CLIENT = 235
    AND t2.CODE_CLIENT = t1.CODE_CLIENT
GROUP BY t1.CODE_FAMILLE
0
 
LVL 6

Author Comment

by:Manuel Marienne-Duchêne
ID: 37842283
CARR      9337438.75      7927816.32
COLL      8023868.76      6150458.08
DIV      1864.32      -1332.06
NATT      440.00      5550.00
OUT      11726.55      185.76
PRO      7763.70      1974.30
TRA      457.38      44.08

not good sum
0
 
LVL 8

Assisted Solution

by:tomvergote
tomvergote earned 400 total points
ID: 37842372
it's not clear to me what the actual expected output is...
but if it's the nulls that are wrong in gman84's suggestion try this
SELECT CODE_FAMILLE,
      CASE ANNEE
            WHEN 'N' THEN SUM(MONTANT)
      END,
      CASE ANNEE
            WHEN 'N-1' THEN SUM(MONTANT)
      END
FROM import_stat_client_produit
WHERE CODE_CLIENT = 235
GROUP BY CODE_FAMILLE
ORDER BY CODE_FAMILLE
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37842379
Trying again:



SELECT t1.CODE_FAMILLE, SUM(t1.MONTANT) AS N, SUM(t2.MONTANT) AS [N-1]
FROM import_stat_client_produit t1 LEFT JOIN
    import_stat_client_produit t2 ON t1.CODE_FAMILLE = t2.CODE_FAMILLE
WHERE t1.CODE_CLIENT = 235 AND t1.ANNEE = 'N' AND (t2.ANNEE = 'N-1' OR t2.ANNEE IS NULL) AND 
    (t2.CODE_CLIENT = 235 OR CODE_CLIENT IS NULL)
GROUP BY t1.CODE_FAMILLE

Open in new window

0
 
LVL 6

Accepted Solution

by:
Manuel Marienne-Duchêne earned 0 total points
ID: 37842470
I Found

SELECT CODE_FAMILLE,
     SUM(CASE WHEN CODE_CLIENT = 235 AND ANNEE = 'N'THEN MONTANT ELSE 0 END) AS N,
     SUM(CASE WHEN CODE_CLIENT = 235 AND ANNEE = 'N-1' THEN MONTANT ELSE 0 END) AS [N-1]
FROM import_stat_client_produit
WHERE CODE_CLIENT = 235
GROUP BY CODE_FAMILLE
ORDER BY CODE_FAMILLE

And the result is
CODE_FA. N                N-1
BAND      0.00      6.75
CARR      74699.51      49860.48
COLL      78665.38      59139.02
DIV      233.04      -666.03
ENTR      0.00      72.66
EVAC      240.30      0.00
NATT      220.00      5550.00
OUT      1302.95      20.64
PRO      776.37      394.86
TRA      228.69      11.02
0
 
LVL 5

Expert Comment

by:gman84
ID: 37842483
Doh! Yes of course
0
 
LVL 6

Author Comment

by:Manuel Marienne-Duchêne
ID: 37842492
thank you all for your help
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question