Results 1 to 1 of 1
  1. #1
    2 Star Lounger
    Join Date
    Aug 2004
    Location
    Withyham, Sussex, England
    Posts
    121
    Thanks
    2
    Thanked 1 Time in 1 Post

    MySql query problem SOLVED

    SOLVED, I created a new view with the current = 0 query and then run the other view on the new view.Worked a treat.




    Good day all. I have a tricky MySql view that need altering and it is beyond me. I need to add gypsum.current = 1 to the first 3 SELECT COUNT queries so that they will not count where "current" = 0 in the table. Hope that is clear...

    The query is :-
    DELIMITER $$

    USE `pattesti_testing`$$

    DROP VIEW IF EXISTS `traffic_light2`$$

    CREATE ALGORITHM=UNDEFINED DEFINER=`pattesti_pattest`@`%` SQL SECURITY DEFINER VIEW `traffic_light2` AS (
    SELECT
    'id' AS `id`,
    (SELECT
    COUNT(0)
    FROM `gypsum`
    WHERE ((`gypsum`.`test_frequency` = '3')
    AND (`gypsum`.`date_of_test` < (NOW() - INTERVAL 3 MONTH)))) AS `tot_3`, ""tot_3,tot_6,tot_12 need to have curent = 1 in the query somehow""
    (SELECT
    COUNT(0)
    FROM `gypsum`
    WHERE ((`gypsum`.`test_frequency` = '6')
    AND (`gypsum`.`date_of_test` < (NOW() - INTERVAL 6 MONTH)))) AS `tot_6`,
    (SELECT
    COUNT(0)
    FROM `gypsum`
    WHERE ((`gypsum`.`test_frequency` = '12')
    AND (`gypsum`.`date_of_test` < (NOW() - INTERVAL 12 MONTH)))) AS `tot_12`,
    (SELECT
    COUNT(0)
    FROM `gypsum`
    WHERE (`gypsum`.`test_frequency` = '3')) AS `items_3`,
    (SELECT
    COUNT(0)
    FROM `gypsum`
    WHERE (`gypsum`.`test_frequency` = '6')) AS `items_6`,
    (SELECT
    COUNT(0)
    FROM `gypsum`
    WHERE (`gypsum`.`test_frequency` = '12')) AS `items_12`)$$

    DELIMITER ;
    Thanks very Much G Styles
    Last edited by gws; 2015-01-22 at 14:26. Reason: error in code,solved

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •