Just nu i M3-nätverket
Jump to content

Snittlista


Poj

Recommended Posts

Jag har resultat lagrat i en tabell för att få ut snitt, jag lagrar totalresultat och antal serier (Bowling).

I tabellen team_samling finns en kolumn borta som är =0 vid he

mma match och =1 vid borta match. 

SELECT team_resultat.medlem_id, team_medlem.namn, sum(resultat)/sum(serier) as snitt, 
 sum(banp)
FROM team_resultat 
JOIN team_medlem ON team_resultat.medlem_id = team_medlem.medlem_id 
JOIN team_samling ON team_resultat.match_id = team_samling.match_id
WHERE datum >= current_date - interval '1' year
GROUP BY namn
ORDER BY snitt desc 

Med denna fråga får jag ut namn på spelaren, snitt och banpoäng.

Men hur får jag fram hemmasnitt och bortasnitt?

 

Edit.

Jag har prövat detta

SELECT team_resultat.medlem_id, team_medlem.namn, sum(resultat)/sum(serier) as snitt, 
avg(case when not borta then resultat/serier end) as hemmasnitt, 
avg(case when borta then resultat/serier end) as bortasnitt, sum(banp)
FROM team_resultat 
JOIN team_medlem ON team_resultat.medlem_id = team_medlem.medlem_id 
JOIN team_samling ON team_resultat.match_id = team_samling.match_id
WHERE datum >= current_date - interval '1' year
GROUP BY namn
ORDER BY snitt desc

Men avg räknar ut det fel.

jag har testat detta:

SELECT team_resultat.medlem_id, team_medlem.namn, sum(resultat)/sum(serier) as snitt, 
case when not borta then sum(resultat)/sum(serier) end as hemmasnitt, 
case when borta then sum(resultat)/sum(serier) end as bortasnitt, sum(banp)
FROM team_resultat 
JOIN team_medlem ON team_resultat.medlem_id = team_medlem.medlem_id 
JOIN team_samling ON team_resultat.match_id = team_samling.match_id
WHERE datum >= current_date - interval '1' year
GROUP BY namn
ORDER BY snitt desc 

Här blir hemmasnitt lika med snitt och bara en person som bara har spelat bortamatcher syns i bortasnitt resten står NULL i kolumnen även om personen har spelat bortamatcher. Jag gjorde en test med

"case when not borta then sum(resultat) end as hemmasnitt" för att se vad som kom ut och det summerar alla resultat i tabellen dvs samma som snitt gör tog inte hänsyn till värdet på borta alls.

 

Vad ska jag göra?

Link to comment
Share on other sites

Testa att vända ut och in på CASE och SUM, dvs något i stil med:

SUM(case when not borta then resultat else 0 end) / SUM(case when not borta then serier else 0 end) as hemmasnitt

 

(Om detta inte fungerar, kom gärna med create table-satserna och några INSERT så att det blir lättare för oss att testa!)

 

 

Dessutom följer du inte generella GROUP BY-regeln:

Vid GROUP BY ska kolumner i select-listan antingen listas i GROUP BY eller användas som argument till aggregat-funktion (t.ex. SUM).

 

Link to comment
Share on other sites

Okey det verkar fungera.

Vad exakt betyder detta?

 

Dessutom följer du inte generella GROUP BY-regeln:

Vid GROUP BY ska kolumner i select-listan antingen listas i GROUP BY eller användas som argument till aggregat-funktion (t.ex. SUM). 

 

 

I denna tabellen har jag resultat som är totalpoängen och antal spelade serier. Jag funderar på om det är den optimala lösningen, jag vill testa att istället ha fyra kolumner serie1,serie2,serie3 och serie4 och lägga in resultatet för varje serie i dom.

Det innebär nya prövningar, jag har summerat kolumnerna med "sum(serie1+serie2+serie3+serie4)" men hur räknar jag antalet värde i kolumnerna?

Och hur räknar jag ut snittet? 

 

EDIT 14-11-05

Så här får jag ut snittet

SELECT team_resultat_1.medlem_id, team_medlem.namn, (sum(serie1+serie2+serie3+serie4)) /(count(serie1)+count(serie2)+count(serie3)+count(serie4)) as snitt 
FROM team_resultat_1 
JOIN team_medlem ON team_resultat_1.medlem_id = team_medlem.medlem_id 
JOIN team_samling ON team_resultat_1.match_id = team_samling.match_id 
WHERE datum >= current_date - interval '1' year 
GROUP BY namn 
ORDER BY snitt desc

Det stämmer inte riktigt har jag inte resultat i alla fyra kolumnerna så blir det fel (NULL). Ett eller två resultat räknas inte ihop utan blir NULL.

Kolumnerna serie1-4 har NULL som standard, ska det vara 0 istället ? eller räknas det av Count i kolumnen?

 

Hur får jag ut hemmasnitt och bortasnitt?

Har försökt med varianter av detta

SUM(case when not borta then (sum(serie1)+sum(serie2)+sum(serie3)+sum(serie4)) else 0 end) / SUM(case when not borta then (count(serie1)+count(serie2)+count(serie3)+count(serie4)) else 0 end) as hemmasnitt

men det fungerar ej får ett felmeddelande: MySQL sa  #1111- Utilisation invalide de la clause GROUP

Vad betyder det och hur löser jag det?

Link to comment
Share on other sites

Exempel på GROUP BY-regeln:

select colA, colB, SUM(colC), MAX(colD)

Här ser du att colC och colD är argument till aggregat-funktioner (funktioner som fungerar på många rader). colA och colB däremot är inte argument till aggregat-funktioner och ska därmed listas i GROUP BY:

 

select colA, colB, SUM(colC), MAX(colD)

from tableX

group by colA, colB

 

(En del databashanterare är mindre petiga och ställer inte detta krav, vilket ger ett något oförutsägbart resultat.)

 

 

NULL-logiken kan vara lite knepig om man är ovan. När man använder SUM för att summera ett antal raders värden så fungerar det så att NULL helt enkelt inte räknas med (blir som 0). Men om man anväder + för att t.ex. addera två kolumn-värden med varandra så blir resultatet NULL om ena (eller båda) värdena är NULL.

 

Dock tycker jag att NULL för icke spelade serier är rätt sätt att spara det som. (Om man använder 0 kan man ju inte skilja på icke spelad och spelad med 0 poäng, vilket inte alls är samma sak.)

 

 

När det gäller SUM(serie(SUM)... så får du tänka till en gång till!

Link to comment
Share on other sites

Tack för svaret.

(sum(serie1)+sum(serie2)+sum(serie3)+sum(serie4)) detta räknar ut resultatet men det blir fel om det är NULL i en kolumn och det beror alltså på "+" tecknet. hur får jag funktionen utan att använda + ?

Jag har letat i mysql manualen men den är svår att hitta i och ger knappt några exempel.

Link to comment
Share on other sites

Du kan ju testa med COALESCE som returnerar första värdet som ej är NULL.

 

T.ex. COALESCE(x, 0) kommer att returnera x så länge x inte är NULL, och värdet 0 om x är NULL.

Link to comment
Share on other sites

COALESCE verkar fungera jag får ut snitt här:  sum(COALESCE(serie1, 0)+COALESCE(serie2, 0)+COALESCE(serie3, 0)+COALESCE(serie4, 0))/(count(serie1)+count(serie2)+count(serie3)+count(serie4)) as snitt

 

Men jag får inte ut hemmasnitt och bortasnitt. I första tabell alternativet jag hade med kolumnerna resultat (total poäng) och serier (antal serier spelade) fungerade detta :

SUM(case when not borta then resultat else 0 end) / SUM(case when not borta then serier else 0 end) as hemmasnitt, 
SUM(case when borta then resultat else 0 end) / SUM(case when borta then serier else 0 end) as bortasnitt

men nu när jag istället har fyra kolumner serie1,serie2,serie3 och serie4 får jag inte till det. Jag får detta felmeddelandet  #1111 - Utilisation invalide de la clause GROUP på denna koden

SUM(case when not borta then sum(COALESCE(serie1, 0)+COALESCE(serie2, 0)+COALESCE(serie3, 0)+COALESCE(serie4, 0)) else 0 end) / SUM(case when not borta then (count(serie1)+count(serie2)+count(serie3)+count(serie4)) else 0 end) as hemmasnitt

Hur ska sql frågan vara?

Link to comment
Share on other sites

Efter mycket slit och sökande så verkar detta fungera:

SELECT team_resultat_1.medlem_id, team_medlem.namn, sum(COALESCE(serie1, 0)+COALESCE(serie2, 0)+COALESCE(serie3, 0)+COALESCE(serie4, 0))/(count(serie1)+count(serie2)+count(serie3)+count(serie4)) as snitt, 
case when not borta then sum(COALESCE(serie1, 0)+COALESCE(serie2, 0)+COALESCE(serie3, 0)+COALESCE(serie4, 0)) else 0 end / case when not borta then (count(serie1)+count(serie2)+count(serie3)+count(serie4)) else 0 end as hemmasnitt,
case when borta then sum(COALESCE(serie1, 0)+COALESCE(serie2, 0)+COALESCE(serie3, 0)+COALESCE(serie4, 0)) else 0 end / case when borta then (count(serie1)+count(serie2)+count(serie3)+count(serie4)) else 0 end as bortasnitt
FROM team_resultat_1 
JOIN team_medlem ON team_resultat_1.medlem_id = team_medlem.medlem_id 
JOIN team_samling ON team_resultat_1.match_id = team_samling.match_id 
WHERE datum >= current_date - interval '1' year 
GROUP BY namn
ORDER BY snitt desc

Nu ska jag stänga av datorn och fira Far.

När jag kommer hem ska jag mata in mer resultat och testa mer.

 

Men ser det rätt ut eller är det något fel på koden? Ja det är det..

Nu när jag matat in alla resultat ser jag att hemmasnitt blir samma som snitt utom för en person som bara spelat bortamatch.

I första tabellen löstes det med : SUM(case when not borta then resultat else 0 end) / SUM(case when not borta then serier else 0 end) as hemmasnitt

Så jag försökte med:  SUM(case when not borta then sum(COALESCE(serie1, 0)+COALESCE(serie2, 0)+COALESCE(serie3, 0)+COALESCE(serie4, 0)) else 0 end) / SUM(case when not borta then (count(serie1)+count(serie2)+count(serie3)+count(serie4)) else 0 end) as hemmasnitt

Men jag får felmeddelande #1111 - Utilisation invalide de la clause GROUP

 

VAD SKA JAG GÖRA?

jag skulle presentera detta på Tisdag.

 

EDIT 2.

Här är create table-satserna och några INSERT

-- phpMyAdmin SQL Dump
-- version 4.1.14
-- http://www.phpmyadmin.net
--
-- Värd: 127.0.0.1
-- Tid vid skapande: 09 nov 2014 kl 19:47
-- Serverversion: 5.6.17
-- PHP-version: 5.5.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Databas: `teamet`
--

-- --------------------------------------------------------

--
-- Tabellstruktur `team_medlem`
--

CREATE TABLE IF NOT EXISTS `team_medlem` (
  `medlem_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `namn` varchar(100) COLLATE utf8_swedish_ci NOT NULL,
  PRIMARY KEY (`medlem_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci COMMENT='Medlems register' AUTO_INCREMENT=36 ;

--
-- Dumpning av Data i tabell `team_medlem`
--

INSERT INTO `team_medlem` (`medlem_id`, `namn`) VALUES
(1, 'Sven-Åke'),
(2, 'Christer'),
(4, 'Sören C'),
(5, 'Jan '),
(6, 'Lars-Göran'),
(7, 'Anders S'),
(8, 'Bengt'),
(9, 'Per-Olof'),
(10, 'Barsom'),
(11, 'Mikael'),
(12, 'Andreas J'),
(13, 'Jonas W'),
(14, 'Sören F'),
(15, 'Daniel'),
(16, 'Stefan'),
(18, 'Lennart'),
(19, 'Jonas N'),
(20, 'Mikael N'),
(21, 'Patrik'),
(22, 'Jörgen'),
(24, 'Anders J'),
(25, 'Andreas L'),
(26, 'Roger'),
(27, 'Peter'),
(28, 'Dado'),
(29, 'Maria'),
(33, 'Mats');

-- --------------------------------------------------------

--
-- Tabellstruktur `team_resultat_1`
--

CREATE TABLE IF NOT EXISTS `team_resultat_1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `medlem_id` int(11) unsigned DEFAULT NULL,
  `serie1` decimal(5,2) unsigned DEFAULT NULL,
  `serie2` decimal(5,2) unsigned DEFAULT NULL,
  `serie3` decimal(5,2) unsigned DEFAULT NULL,
  `serie4` decimal(5,2) unsigned DEFAULT NULL,
  `banp` int(1) unsigned DEFAULT NULL,
  `match_id` int(11) unsigned DEFAULT NULL,
  `resultat_sparad` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci COMMENT='Match resultat' AUTO_INCREMENT=53 ;

--
-- Dumpning av Data i tabell `team_resultat_1`
--

INSERT INTO `team_resultat_1` (`id`, `medlem_id`, `serie1`, `serie2`, `serie3`, `serie4`, `banp`, `match_id`, `resultat_sparad`) VALUES
(1, 6, '175.00', '202.00', '212.00', '211.00', 3, 2, '2014-10-11 17:11:37'),
(2, 7, '215.00', '174.00', '189.00', '212.00', 3, 2, '2014-10-11 17:11:37'),
(3, 11, '168.00', '215.00', '195.00', '202.00', 2, 2, '2014-10-11 17:11:37'),
(4, 12, '184.00', '195.00', '176.00', '215.00', 2, 2, '2014-10-11 17:11:37'),
(5, 14, '234.00', '175.00', '168.00', '183.00', 3, 2, '2014-10-11 17:11:37'),
(6, 15, '193.00', '185.00', '206.00', '166.00', 2, 2, '2014-10-11 17:11:37'),
(7, 24, '169.00', '198.00', '186.00', '187.00', 2, 2, '2014-10-11 17:11:37'),
(8, 25, '175.00', '135.00', NULL, '195.00', 1, 2, '2014-10-11 17:11:37'),
(9, 27, NULL, NULL, '165.00', NULL, 0, 2, '2014-10-11 17:11:37'),
(10, 8, '192.00', '184.00', '165.00', '159.00', 2, 1, '2014-10-26 14:29:20'),
(11, 9, '175.00', '168.00', '159.00', '188.00', 3, 1, '2014-10-26 14:29:20'),
(12, 10, '170.00', '185.00', '135.00', '190.00', 2, 1, '2014-10-26 14:29:20'),
(13, 13, '158.00', '185.00', '195.00', '132.00', 2, 1, '2014-10-26 14:29:20'),
(14, 19, '165.00', '178.00', '169.00', '148.00', 2, 1, '2014-10-26 14:29:20'),
(15, 20, '185.00', '145.00', '170.00', '150.00', 1, 1, '2014-10-26 14:29:20'),
(16, 22, '145.00', '168.00', '172.00', '155.00', 2, 1, '2014-10-26 14:29:20'),
(17, 25, '160.00', '170.00', '165.00', '145.00', 1, 1, '2014-10-26 14:29:20'),
(19, 1, '175.00', '203.00', '218.00', '204.00', 2, 3, '2014-10-26 14:31:00'),
(20, 2, '245.00', '189.00', '150.00', '206.00', 2, 3, '2014-10-26 14:31:00'),
(21, 4, '196.00', '189.00', '204.00', '191.00', 2, 3, '2014-10-26 14:31:00'),
(22, 5, '196.00', '175.00', '199.00', '200.00', 2, 3, '2014-10-26 14:31:00'),
(23, 7, '215.00', '186.00', '175.00', '184.00', 2, 3, '2014-10-26 14:31:00'),
(24, 8, '184.00', '195.00', '178.00', '193.00', 2, 3, '2014-10-26 14:31:00'),
(25, 14, '158.00', '195.00', '203.00', '184.00', 3, 3, '2014-10-26 14:31:00'),
(26, 15, '189.00', '143.00', NULL, '168.00', 1, 3, '2014-10-26 14:31:00'),
(27, 18, NULL, NULL, '150.00', NULL, 1, 3, '2014-10-26 14:31:00'),
(28, 2, '187.00', '245.00', '196.00', '172.00', 2, 4, '2014-10-26 16:57:47'),
(29, 6, '236.00', '187.00', '195.00', '172.00', 2, 4, '2014-10-26 16:57:47'),
(30, 11, '183.00', '205.00', '199.00', '193.00', 2, 4, '2014-10-26 16:57:47'),
(31, 12, '182.00', '186.00', '199.00', '203.00', 2, 4, '2014-10-26 16:57:47'),
(32, 14, '172.00', '204.00', '196.00', '188.00', 2, 4, '2014-10-26 16:57:47'),
(33, 15, '188.00', '194.00', '189.00', '179.00', 2, 4, '2014-10-26 16:57:47'),
(34, 25, '185.00', '196.00', '175.00', '184.00', 2, 4, '2014-10-26 16:57:47'),
(35, 33, '213.00', '202.00', '175.00', '140.00', 2, 4, '2014-10-26 16:57:47'),
(36, 2, '189.00', '256.00', '183.00', '172.00', 2, 5, '2014-10-26 16:58:26'),
(37, 6, '193.00', '199.00', '204.00', '194.00', 2, 5, '2014-10-26 16:58:26'),
(38, 11, '213.00', '199.00', '186.00', '182.00', 2, 5, '2014-10-26 16:58:26'),
(39, 12, '175.00', '189.00', '188.00', '218.00', 2, 5, '2014-10-26 16:58:26'),
(40, 14, '168.00', '234.00', '185.00', '173.00', 2, 5, '2014-10-26 16:58:26'),
(41, 15, '196.00', '215.00', '189.00', '150.00', 2, 5, '2014-10-26 16:58:26'),
(42, 25, '205.00', '187.00', '169.00', '179.00', 2, 5, '2014-10-26 16:58:26'),
(43, 33, '173.00', '189.00', '196.00', '172.00', 2, 5, '2014-10-26 16:58:26'),
(44, 10, '163.00', '174.00', '189.00', '174.00', 2, 6, '2014-10-26 16:59:15'),
(45, 13, '159.00', '178.00', '182.00', '171.00', 2, 6, '2014-10-26 16:59:15'),
(46, 16, '201.00', '148.00', '174.00', '157.00', 2, 6, '2014-10-26 16:59:15'),
(47, 19, '185.00', '169.00', '168.00', '148.00', 2, 6, '2014-10-26 16:59:15'),
(48, 18, '146.00', '175.00', '169.00', '170.00', 2, 6, '2014-10-26 16:59:15'),
(49, 21, '178.00', '165.00', '159.00', '148.00', 2, 6, '2014-10-26 16:59:15'),
(50, 26, '136.00', '159.00', '182.00', '163.00', 2, 6, '2014-10-26 16:59:15'),
(51, 27, '123.00', NULL, '189.00', '208.00', 2, 6, '2014-10-26 16:59:15'),
(52, 29, NULL, '160.00', NULL, NULL, 2, 6, '2014-10-26 16:59:15');

-- --------------------------------------------------------

--
-- Tabellstruktur `team_samling`
--

CREATE TABLE IF NOT EXISTS `team_samling` (
  `match_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lag_id` int(11) DEFAULT NULL,
  `omg` int(2) unsigned DEFAULT NULL,
  `borta` tinyint(1) unsigned DEFAULT '0',
  `datum` date DEFAULT NULL,
  `tid` time DEFAULT NULL,
  `motstandare` varchar(50) COLLATE utf8_swedish_ci DEFAULT NULL,
  `samling` varchar(6) COLLATE utf8_swedish_ci DEFAULT NULL,
  `ovrigt` varchar(50) COLLATE utf8_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`match_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci COMMENT='Samling' AUTO_INCREMENT=14 ;

--
-- Dumpning av Data i tabell `team_samling`
--

INSERT INTO `team_samling` (`match_id`, `lag_id`, `omg`, `borta`, `datum`, `tid`, `motstandare`, `samling`, `ovrigt`) VALUES
(1, 1, 1, 0, '2014-10-28', '12:15:00', 'Forsheda', '11:30', 'match dräkt'),
(2, 2, 1, 0, '2014-10-27', '10:00:00', 'Jönköping KK', '09:15', ''),
(3, 3, 3, 0, '2014-10-30', '15:20:00', 'Alvesta', '14:30', ''),
(4, 2, 2, 1, '2014-11-05', '12:00:00', 'Sävsjö', '09:15', 'Buss'),
(5, 2, 2, 1, '2014-11-05', '14:00:00', 'Eksjö', NULL, NULL),
(6, 3, 2, 0, '2014-11-06', '11:15:00', 'Jönköping kk', '10:30', NULL),
(11, 1, 20, 1, '2014-11-05', '12:30:00', 'test borta 2', '09:30', NULL),
(13, 1, 22, 0, '2014-11-06', '10:00:00', 'Test hemma', '09:00', NULL);

Link to comment
Share on other sites

Inga svar betyder att det inte finns någon lösning.

Jag får helt enkelt slå ihop mina tabeller, jag har en lösning till tabellen med total resultat och antal serier men jag vill ha dom enskilda serierna också för att kunna göra en toplista med dom 5 högsta slagningarna mm.

Så jag slår ihop det: serie1,serie2,serie3,serie4,resultat,antal serier, banpoäng.

Kan jag förenkla inmatningen i formuläret med att tex räkna ut resultat automatiskt genom att summera serie1-serie4 och få in summan i ett dolt fält ?

 

Hur gör jag isåfall?

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...