Solved

SQL REQUEST PROBLEM

Posted on 2012-04-13
13
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 93

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 93

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

626 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