Android: How to get count of unique values in a table, where unique value is the combination of two-CodePudding

I am using this query to get values based on a list

Query :

select * from Assignment_Data_RAD where Rfid IN ( '34E59808D8400410C2048F48','3460881C004610224927D013','3C60881C00461020420FD000','3C60881C00461020420C107D','3DD00000000000103D004188','3460881C00461020420FD000' )

Result:

The desired result is the Qty count based on The GS_1_BARCODE and Category

Category             |        Gs1     |    Qty
shoppingbags 15/1000 | 62870164212311 |   1 
shoppingbags 15/1000 | 628701642999   |   1 
shoppingbags 15/1000 | 628701642123   |   2 
Thermal Bags 16      | 628701642999   |   2

I also need all the other columns along with the above data.

What else do I need to add to my current query to get the sum of quantity based on the category and gs1 barcode column in the list of Rfid's

CodePudding user response：

You can't group something and retain all of the original data. That means no grouping.

Even in your example, you can have only those columns with which you group the columns.

SELECT Category, Gs1, COUNT(*) as Qty FROM Assignment_Data_RAD GROUP BY Category, Gs1;

CodePudding user response：

What else do I need to add to my current query to get the sum of quantity based on the category and gs1 barcode column in the list of Rfid's

You can use the

I also need all the other columns along with the above data

Although you could group_concat all the other columns it could be difficult associating the values.

An alternative approach could be to to produce a combination of the groups, as header rows along with the actual data rows e.g. something like:-

Noting that the values in the header row (the first has had the arbitrary values highlighted) should (unless they form part of the grouping) are arbitrary values from one of the grouped rows.
- They are arbitrary values because for a group you have multiple underlying values for each column but a single value representation of those columns

The above could be created using:-

WITH
    headers AS (
        SELECT 0 AS flag,*, 
            sum(qty) AS quantity_sum 
        FROM Assignment_Data_RAD
        WHERE rfid IN ( 
            '34E59808D8400410C2048F48',
            '3460881C004610224927D013',
            '3C60881C00461020420FD000',
            '3C60881C00461020420C107D',
            '3DD00000000000103D004188',
            '3460881C00461020420FD000' 
        )
        GROUP BY category,GS_1_BARCODE
    ),
    alldata AS (
        SELECT 1 AS flag,*,null
        FROM Assignment_Data_RAD
        WHERE rfid IN ( 
            '34E59808D8400410C2048F48',
            '3460881C004610224927D013',
            '3C60881C00461020420FD000',
            '3C60881C00461020420C107D',
            '3DD00000000000103D004188',
            '3460881C00461020420FD000' 
        )
    )
SELECT 
    CASE WHEN flag THEN 'DATAROW' ELSE 'HEADER' END AS type, * 
FROM headers 
UNION ALL 
SELECT 
    CASE WHEN flag THEN 'DATAROW' ELSE 'HEADER' END AS type,* 
FROM alldata 
ORDER BY category,gs_1_BARCODE,flag;

noting that this uses CTE's (Common Table Expressions) (headers and alldata are the CTE's) which are effectively temporary tables that exist for the duration of the execution.