SQL REQUEST PROBLEM

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
LVL 6
Manuel Marienne-DuchêneITMAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MHITSupportCommented:
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
Manuel Marienne-DuchêneITMAuthor Commented:
Not functionnaly
Patrick MatthewsCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Manuel Marienne-DuchêneITMAuthor Commented:
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
gman84Commented:
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
Manuel Marienne-DuchêneITMAuthor Commented:
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
gman84Commented:
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
Manuel Marienne-DuchêneITMAuthor Commented:
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
tomvergoteCommented:
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
Patrick MatthewsCommented:
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

Manuel Marienne-DuchêneITMAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gman84Commented:
Doh! Yes of course
Manuel Marienne-DuchêneITMAuthor Commented:
thank you all for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.