#1
  1. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,246
    Rep Power
    220

    Is it correct to use SUM and GROUP BY in the same query?


    Is it approved by SimplySql people?

    Seems right when I look at the answers.

    Code:
     SELECT Sum(pks_amount) AS total,
           Count(*)        AS quantity
    FROM   transactions
    WHERE  pks_product_id = 6212
           AND pks_mode = 'live'
           AND pks_event = 'sales'
    Code:
    CREATE TABLE `transactions` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `transaction_time` varchar(255) NOT NULL,
      `receipt` varchar(255) NOT NULL,
      `transaction_type` varchar(255) NOT NULL,
      `affiliate` varchar(255) NOT NULL,
      `payment_method` varchar(255) NOT NULL,
      `item_no` int(11) NOT NULL,
      `recurring` varchar(255) NOT NULL,
      `line_item_type` varchar(255) NOT NULL,
      `first_name` varchar(255) NOT NULL,
      `stripe_price` decimal(6,2) DEFAULT NULL,
      `email` varchar(255) NOT NULL,
      `upsell_original_receipt` varchar(255) NOT NULL,
      `upsell_flow_id` int(11) unsigned zerofill NOT NULL,
      `username` varchar(255) NOT NULL,
      `notes` text NOT NULL,
      `ontraport_user_id` int(11) DEFAULT NULL,
      `payment_system` varchar(255) DEFAULT NULL,
      `stripe_order_id` varchar(255) DEFAULT NULL,
      `stripe_description` text,
      `ontraport_id_from_srtipe` int(11) DEFAULT NULL,
      `stripe_meta_data_contact_id` int(11) DEFAULT NULL,
      `status_in_our_db` varchar(255) DEFAULT 'active',
      `jvzoo_cprodtitle` varchar(255) DEFAULT NULL,
      `jvzoo_cproditem` varchar(255) DEFAULT NULL,
      `jvzoo_ctransaffiliate` varchar(255) DEFAULT NULL,
      `jvzoo_ctransamount` int(11) DEFAULT NULL,
      `jvzoo_ctranspaymentmethod` varchar(4) DEFAULT NULL,
      `jvzoo_ctransvendor` varchar(255) DEFAULT NULL,
      `jvzoo_cupsellreceipt` varchar(255) DEFAULT NULL,
      `jvzoo_caffitid` varchar(255) DEFAULT NULL,
      `date` date DEFAULT NULL,
      `transaction_id` varchar(255) DEFAULT NULL,
      `stats_in_our_database` varchar(255) DEFAULT 'inactive',
      `op_invoice_id` int(11) DEFAULT NULL,
      `clickbank_transaction` int(11) DEFAULT NULL,
      `jvzoo_transaction` int(11) DEFAULT NULL,
      `pks_transaction` int(11) DEFAULT NULL,
      `pks_amount` decimal(6,2) DEFAULT NULL,
      `pks_item_number` int(11) DEFAULT NULL,
      `pks_mode` varchar(255) DEFAULT NULL,
      `pks_payment_processor` varchar(255) DEFAULT NULL,
      `pks_buyer_ip` varchar(255) DEFAULT NULL,
      `pks_event` varchar(255) DEFAULT NULL,
      `pks_transaction_id` varchar(255) DEFAULT NULL,
      `pks_tracking_id` varchar(255) DEFAULT NULL,
      `pks_product_id` int(11) DEFAULT NULL,
      `pks_product_name` varchar(255) DEFAULT NULL,
      `pks_buyer_email` varchar(255) DEFAULT NULL,
      `pks_transaction_time` varchar(255) DEFAULT NULL,
      `pks_invoice_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3582 DEFAULT CHARSET=utf8 COMMENT='ALTER TABLE employees\n  ADD last_name VARCHAR(50);\n\n`id` int(11) NOT NULL AUTO_INCREMENT,\n  `transaction_time` varchar(255) NOT NULL,\n  `receipt` varchar(255) NOT NULL,\n  `transaction_type` varchar(255) NOT NULL,\n  `affiliate` varchar(255) NOT NULL,\n  `payment_method` varchar(255) NOT NULL,\n  `item_no` int(11) NOT NULL,\n  `recurring` varchar(255) NOT NULL,\n  `line_item_type` varchar(255) NOT NULL,\n  `first_name` varchar(255) NOT NULL,\n  `stripe_price` decimal(4,2) DEFAULT NULL,\n  `email` varchar(255) NOT NULL,\n  `upsell_original_receipt` varchar(255) NOT NULL,\n  `upsell_flow_id` int(11) unsigned zerofill NOT NULL,\n  `username` varchar(255) NOT NULL,\n  `notes` text NOT NULL,\n  `ontraport_user_id` int(11) DEFAULT NULL,\n  `payment_system` varchar(255) DEFAULT NULL,\n  `stripe_order_id` varchar(255) DEFAULT NULL,\n  `stripe_description` text,\n  `ontraport_id_from_srtipe` int(11) DEFAULT NULL,\n  `stripe_meta_data_contact_id` int(11) DEFAULT NULL,\n  `status_in_our_db` varchar(255) DEFAULT ''active'',\n  `jvzoo_cprodtitle` varchar(255) DEFAULT NULL,\n  `jvzoo_cproditem` varchar(255) DEFAULT NULL,\n  `jvzoo_ctransaffiliate` varchar(255) DEFAULT NULL,\n  `jvzoo_ctransamount` int(11) DEFAULT NULL,\n  `jvzoo_ctranspaymentmethod` varchar(4) DEFAULT NULL,\n  `jvzoo_ctransvendor` varchar(255) DEFAULT NULL,\n  `jvzoo_cupsellreceipt` varchar(255) DEFAULT NULL,\n  `jvzoo_caffitid` varchar(255) DEFAULT NULL,';
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645

    😭😣😓😔💩


    >asks whether it's correct to use an aggregate function in an aggregate query

    >asks about a query with sum and group by
    >posts query that uses sum but not group by


    >posts a show create table statement for a table
    >table's comment is part alter table query, part list of table columns
    Last edited by requinix; October 23rd, 2017 at 06:09 AM.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by English Breakfast Tea
    Is it approved by SimplySql people?
    yes, using SUM and GROUP BY in the same query is fine

    i am surprised you had to ask, because that is basic sql... i mean, really basic

    i have a feeling there is something else on your mind
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo