Hoe 2 kolommen vermenigvuldigen?

Ik voer een query uit op Gegevensverkenner :

SELECT
  Name,
  COUNT(*) Qty,
  Points = CASE Name
    WHEN 'Altruist' THEN 5
    WHEN 'Analytical' THEN 5
    WHEN 'Announcer' THEN 5
    WHEN 'Autobiographer' THEN 5
    WHEN 'Benefactor' THEN 5
    WHEN 'Caucus' THEN 5
    WHEN 'Citizen Patrol' THEN 5
    WHEN 'Cleanup' THEN 5
    WHEN 'Commentator' THEN 5
    WHEN 'Critic' THEN 5
    WHEN 'Custodian' THEN 5
    WHEN 'Disciplined' THEN 5
    WHEN 'Editor' THEN 5
    WHEN 'Excavator' THEN 5
    WHEN 'Informed' THEN 5
    WHEN 'Investor' THEN 5
    WHEN 'Mortarboard' THEN 5
    WHEN 'Nice Answer' THEN 5
    WHEN 'Nice Question' THEN 5
    WHEN 'Organizer' THEN 5
    WHEN 'Peer Pressure' THEN 5
    WHEN 'Popular Question' THEN 5
    WHEN 'Precognitive' THEN 5
    WHEN 'Promotor' THEN 5
    WHEN 'Proofreader' THEN 5
    WHEN 'Quorum' THEN 5
    WHEN 'Revival' THEN 5
    WHEN 'Scholar' THEN 5
    WHEN 'Self-Learner' THEN 5
    WHEN 'Student' THEN 5
    WHEN 'Suffrage' THEN 5
    WHEN 'Supporter' THEN 5
    WHEN 'Synonymizer' THEN 5
    WHEN 'Tag Editor' THEN 5
    WHEN 'Talkative' THEN 5
    WHEN 'Teacher' THEN 5
    WHEN 'Tumbleweed' THEN 5
    WHEN 'Vox Populi' THEN 5
    WHEN 'Archaeologist' THEN 10
    WHEN 'Beta' THEN 10
    WHEN 'Booster' THEN 10
    WHEN 'Civic Duty' THEN 10
    WHEN 'Constituent' THEN 10
    WHEN 'Convention' THEN 10
    WHEN 'Deputy' THEN 10
    WHEN 'Enlightened' THEN 10
    WHEN 'Enthusiast' THEN 10
    WHEN 'Epic' THEN 10
    WHEN 'Favorite Question' THEN 10
    WHEN 'Generalist' THEN 10
    WHEN 'Good Answer' THEN 10
    WHEN 'Good Question' THEN 10
    WHEN 'Guru' THEN 10
    WHEN 'Necromancer' THEN 10
    WHEN 'Notable Question' THEN 10
    WHEN 'Outspoken' THEN 10
    WHEN 'Pundit' THEN 10
    WHEN 'Research Assistant' THEN 10
    WHEN 'Reviewer' THEN 10
    WHEN 'Sportsmanship' THEN 10
    WHEN 'Strunk & White' THEN 10
    WHEN 'Taxonomist' THEN 10
    WHEN 'Tenacious' THEN 10
    WHEN 'Yearling' THEN 10
    WHEN 'Copy Editor' THEN 20
    WHEN 'Electorate' THEN 20
    WHEN 'Famous Question' THEN 20
    WHEN 'Fanatic' THEN 20
    WHEN 'Great Answer' THEN 20
    WHEN 'Great Question' THEN 20
    WHEN 'Legendary' THEN 20
    WHEN 'Marshal' THEN 20
    WHEN 'Populist' THEN 20
    WHEN 'Publicist' THEN 20
    WHEN 'Reversal' THEN 20
    WHEN 'Stellar Question' THEN 20
    WHEN 'Steward' THEN 20
    WHEN 'Unsung Hero' THEN 20
    ELSE 0
  END,
  Qty * Points AS Total
FROM Badges
WHERE UserId = 1563422
GROUP BY Name
ORDER BY COUNT(*) DESC

Dit werkt allemaal behalve wanneer , Aantal * Punten AS Totaal is toegevoegd (haal die weg van de bovenstaande query naar voer de query uit succesvol).

De fout die ik krijg is:

Ongeldige kolomnaam ' Aantal '.

     

Ongeldige kolomnaam ' Punten '.

Hoe kan ik aantal en punten als totaal vermenigvuldigen?

0
ja ru de

1 antwoord

Gebruik een subquery of een CTE zoals dit:

WITH CTE
AS
(
  SELECT
    Name,
    COUNT(*) Qty,
    Points = CASE Name
      WHEN 'Altruist' THEN 5
      WHEN 'Analytical' THEN 5
      WHEN 'Announcer' THEN 5
      WHEN 'Autobiographer' THEN 5
      WHEN 'Benefactor' THEN 5
      WHEN 'Caucus' THEN 5
      WHEN 'Citizen Patrol' THEN 5
      WHEN 'Cleanup' THEN 5
      WHEN 'Commentator' THEN 5
      WHEN 'Critic' THEN 5
      WHEN 'Custodian' THEN 5
      WHEN 'Disciplined' THEN 5
      WHEN 'Editor' THEN 5
      WHEN 'Excavator' THEN 5
      WHEN 'Informed' THEN 5
      WHEN 'Investor' THEN 5
      WHEN 'Mortarboard' THEN 5
      WHEN 'Nice Answer' THEN 5
      WHEN 'Nice Question' THEN 5
      WHEN 'Organizer' THEN 5
      WHEN 'Peer Pressure' THEN 5
      WHEN 'Popular Question' THEN 5
      WHEN 'Precognitive' THEN 5
      WHEN 'Promotor' THEN 5
      WHEN 'Proofreader' THEN 5
      WHEN 'Quorum' THEN 5
      WHEN 'Revival' THEN 5
      WHEN 'Scholar' THEN 5
      WHEN 'Self-Learner' THEN 5
      WHEN 'Student' THEN 5
      WHEN 'Suffrage' THEN 5
      WHEN 'Supporter' THEN 5
      WHEN 'Synonymizer' THEN 5
      WHEN 'Tag Editor' THEN 5
      WHEN 'Talkative' THEN 5
      WHEN 'Teacher' THEN 5
      WHEN 'Tumbleweed' THEN 5
      WHEN 'Vox Populi' THEN 5
      WHEN 'Archaeologist' THEN 10
      WHEN 'Beta' THEN 10
      WHEN 'Booster' THEN 10
      WHEN 'Civic Duty' THEN 10
      WHEN 'Constituent' THEN 10
      WHEN 'Convention' THEN 10
      WHEN 'Deputy' THEN 10
      WHEN 'Enlightened' THEN 10
      WHEN 'Enthusiast' THEN 10
      WHEN 'Epic' THEN 10
      WHEN 'Favorite Question' THEN 10
      WHEN 'Generalist' THEN 10
      WHEN 'Good Answer' THEN 10
      WHEN 'Good Question' THEN 10
      WHEN 'Guru' THEN 10
      WHEN 'Necromancer' THEN 10
      WHEN 'Notable Question' THEN 10
      WHEN 'Outspoken' THEN 10
      WHEN 'Pundit' THEN 10
      WHEN 'Research Assistant' THEN 10
      WHEN 'Reviewer' THEN 10
      WHEN 'Sportsmanship' THEN 10
      WHEN 'Strunk & White' THEN 10
      WHEN 'Taxonomist' THEN 10
      WHEN 'Tenacious' THEN 10
      WHEN 'Yearling' THEN 10
      WHEN 'Copy Editor' THEN 20
      WHEN 'Electorate' THEN 20
      WHEN 'Famous Question' THEN 20
      WHEN 'Fanatic' THEN 20
      WHEN 'Great Answer' THEN 20
      WHEN 'Great Question' THEN 20
      WHEN 'Legendary' THEN 20
      WHEN 'Marshal' THEN 20
      WHEN 'Populist' THEN 20
      WHEN 'Publicist' THEN 20
      WHEN 'Reversal' THEN 20
      WHEN 'Stellar Question' THEN 20
      WHEN 'Steward' THEN 20
      WHEN 'Unsung Hero' THEN 20
      ELSE 0
    END
  FROM Badges
  WHERE UserId = 1563422
  GROUP BY Name
  )
  SELECT *, QTY * Points AS Total
  FROM CTE;

Demo in gegevensverkenner

1
toegevoegd
Werkt perfect, heel erg bedankt Mahmoud !!
toegevoegd de auteur Danny Beckett, de bron
... Ik denk niet dat u misschien weet hoe u een GrandTotal krijgt? chat.stackoverflow.com/rooms/26206/&ellellline;
toegevoegd de auteur Danny Beckett, de bron
@DannyBeckett - Je bent altijd welkom :)
toegevoegd de auteur Mahmoud Gamal, de bron