Home / Questions / 274369
Accepted
Pablo Grassi
Asked: 2020-06-25 11:16:41 +0800 CST 2020-06-25 11:16:41 +0800 CST

Combine 2 MYSQL PHP queries

  • 772

I have these 2 queries in phpwhich I use them to show a table, the first groups all the results by the idlargest registered in the database, based on the field u nique_vendor_identifierand the second query, adds the field paymentand groups it based on the field, unique_vendor_identifierignoring the duplicate entries that are exactly the same in all of their rows.

What I want is to join the 2 queries into one, that is, to combine them, so that the table continues to show grouped by unique_vendor_identifierthe last of that identifier in all its rows and in turn shows me in the field paymentthe sum of the field paymentgrouped by unique_vendor_identifier.

question 1

SELECT * 
FROM jsonapple 
WHERE id IN ( SELECT MAX(id) FROM jsonapple GROUP BY unique_vendor_identifier)

query 2

SELECT payment, SUM(payment) 
FROM (SELECT unique_vendor_identifier, payment 
FROM jsonapple 
GROUP BY unique_vendor_identifier, payment) A group by unique_vendor_identifier

TABLE

CREATE TABLE `jsonapple` (
  `id` int(255) unsigned NOT NULL AUTO_INCREMENT,
  `latest_receipt` varchar(8000) DEFAULT NULL,
  `original_purchase_date_pst` varchar(255) DEFAULT NULL,
  `quantity` int(100) DEFAULT NULL,
  `unique_vendor_identifier` varchar(255) DEFAULT NULL,
  `original_purchase_date_ms` bigint(255) DEFAULT NULL,
  `expires_date_formatted` varchar(255) DEFAULT NULL,
  `is_in_intro_offer_period` varchar(255) DEFAULT NULL,
  `purchase_date_ms` bigint(255) DEFAULT NULL,
  `expires_date_formatted_pst` varchar(255) DEFAULT NULL,
  `is_trial_period` varchar(100) DEFAULT NULL,
  `item_id` bigint(255) DEFAULT NULL,
  `unique_identifier` varchar(255) DEFAULT NULL,
  `original_transaction_id` bigint(255) DEFAULT NULL,
  `expires_date` bigint(255) DEFAULT NULL,
  `app_item_id` bigint(255) DEFAULT NULL,
  `transaction_id` bigint(255) DEFAULT NULL,
  `bvrs` int(100) DEFAULT NULL,
  `web_order_line_item_id` bigint(255) DEFAULT NULL,
  `version_external_identifier` bigint(255) DEFAULT NULL,
  `bid` varchar(255) DEFAULT NULL,
  `product_id` varchar(255) DEFAULT NULL,
  `purchase_date` varchar(255) DEFAULT NULL,
  `purchase_date_pst` varchar(255) DEFAULT NULL,
  `original_purchase_date` varchar(255) DEFAULT NULL,
  `environment` varchar(100) DEFAULT NULL,
  `auto_renew_status` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `auto_renew_product_id` varchar(255) DEFAULT NULL,
  `notification_type` varchar(255) DEFAULT NULL,
  `status` varchar(100) DEFAULT NULL,
  `status_code` int(20) DEFAULT NULL,
  `campaign` varchar(1000) DEFAULT NULL,
  `traffic_source` varchar(255) DEFAULT NULL,
  `expiration_intent` int(11) DEFAULT NULL,
  `is_in_billing_retry_period` int(11) DEFAULT NULL,
  `acquisition_campaign` varchar(20) DEFAULT NULL,
  `payment` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
php

2 Answers

  1. SELECT  jsonapple.unique_vendor_identifier, SUM(
  SELECT jsonapple.payment 
  FROM jsonapple 
  GROUP BY
  jsonapple.unique_vendor_identifier
) AS total_pagos_vendor
FROM jsonapple
ORDER_BY jsonapple.id DESC

    I haven't tried it because I don't have the tables, but I think it's this: First you select the unique_vendor_identifier and the sum of all payments grouped by unique_vendor_identifier. Then you order everything by descending id and thus you will have the sum of all the payments of each vendor, ordered by id (of the payment I suppose)

    The table would look like this:

    unique_vendor_identifier | total_pagos_vendor

    I can't specify more from the head, it could give you an error, if so, comment on it.

    • 1
  2. Best Answer

    Ok I created the structure, I put data in it and in the end this is my query:

    SELECT
    jsonapple.unique_vendor_identifier,
    sum(jsonapple.payment) AS total_pagos_vendor,
    max(jsonapple.id) AS ultimo_payment_vendor
FROM
    jsonapple
GROUP BY
    jsonapple.unique_vendor_identifier
ORDER BY
    ultimo_payment_vendor DESC

    What I have done is add the totals, grouping by unique_vendor_identifier, and I have also selected the last payment id corresponding to each unique_vendor_identifier to then order in descending order by that "field".

    • 1